JDBC增删查改示例

ModelTable

只是辅助作用

public class Table {
	final static int MAX_PEOPLE = 12;
	final static int MIN_PEOPLE = 2;
	private int table_id;
	private int table_status; 
	private int people;
	
	public String getTable_name() {
		return table_name;
	}
	public void setTable_name(String table_name) {
		this.table_name = table_name;
	}
	private String table_name;
	
	public int getTable_id() {
		return table_id;
	}
	public void setTable_id(int tableId) {
		table_id = tableId;
	}
	public int getTable_status() {
		return table_status;
	}
	public void setTable_status(int tableStatus) {
		table_status = tableStatus;
	}
	public int getPeople() {
		return people;
	}
	public void setPeople(int people) {
		this.people = people;
	}
}

connection

获取数据库链接

 

try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(URL, USER, PWD);
		return conn;
	} catch (Exception ex) {
		System.out.println("Error : " + ex.toString());
	}

新增操作

public int addTable(Table table) {
		int flag = 0;
		try {
			StringBuffer sb = new StringBuffer("insert into");
			sb.append(" r_table ");
			sb.append(" (table_status, table_people, table_name) values (");
			sb.append(table.getTable_status());
			sb.append(",");
			sb.append(table.getPeople());
			sb.append(",'");
			sb.append(table.getTable_name());
			sb.append("')");
			
			System.out.println(sb.toString());
			Statement stmt = null;
			stmt = conn.getConnection().createStatement();
			stmt.executeUpdate(sb.toString());
		} catch (SQLException e) {
			flag = 1;
			e.printStackTrace();
		} finally {
			try {
				conn.closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

 删除操作

public int removeTableById(int id) {
		int flag = 0;
		try {
			StringBuffer sb = new StringBuffer("delete from r_table where table_id=");
			sb.append(id);
			
			Statement stmt = null;
			stmt = conn.getConnection().createStatement();
			stmt.executeUpdate(sb.toString());
		} catch (SQLException e) {
			flag = 1;
			e.printStackTrace();
		} finally {
			try {
				conn.closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

更新操作

public int updateTable(Table table) {
		int flag = 0;
		try {
			StringBuffer sb = new StringBuffer("update");
			sb.append(" r_table ");
			sb.append(" set table_status=");
			sb.append(table.getTable_status());
			sb.append(", table_people=");
			sb.append(table.getPeople());
			sb.append(", table_name='");
			sb.append(table.getTable_name());
			sb.append("' where table_id=");
			sb.append(table.getTable_id());
			
			Statement stmt = null;
			stmt = conn.getConnection().createStatement();
			stmt.executeUpdate(sb.toString());
		} catch (SQLException e) {
			flag = 1;
			e.printStackTrace();
		} finally {
			try {
				conn.closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

查询操作

可以查询单个，也可以查询多个

public Table getTableById(int id) {
		Statement stmt;
		Table t = new Table();
		try {
			stmt = conn.getConnection().createStatement();
			// excuteing sql language
			ResultSet rs = stmt.executeQuery("select * from r_table where table_id=" + id);
			while (rs.next()) {
				t.setTable_id(rs.getInt("table_id"));
				t.setTable_name(rs.getString("table_name"));
				t.setTable_status(rs.getInt("table_status"));
				t.setPeople(rs.getInt("table_people"));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return t;
	}