java的增、删、改、查与及批量增加

其中涉及的数据库的连接与关闭，Statement的关闭，ResultSet的关闭，请参考文章http://blog.csdn.net/mrw456/article/details/49718235

本例使用Mysql数据库，以宿舍室表为例

宿舍室表sql：

CREATE TABLE dorm(dormnum INT NOT NULL PRIMARY KEY,dormtext TEXT NOT NULL); 

Dorm.java

public class Dorm {
	private int dormnum;
	private String dormtext;
	public Dorm() {
		super();
	}
	public Dorm(int dormnum, String dormtext) {
		super();
		this.dormnum = dormnum;
 		this.dormtext = dormtext;
	}
 	public int getDormnum() {
		return dormnum;
 	}
  	public void setDormnum(int dormnum) {
 		this.dormnum = dormnum;
	}
 	public String getDormtext() {
		return dormtext;
	 }
 	public void setDormtext(String dormtext) {
		this.dormtext = dormtext;
	}
	
}

1、增加数据

public boolean dormadd(Dorm dorm) {
		Connection connection=MysqlOperation.getConnection();
		String addSQL="insert into dorm(dormnum,"+
		"dormtext) values(?,?)";
		PreparedStatement pstmt=null;
		try {
			connection.setAutoCommit(false); //设置自动提交为false,如果出现异常进行回滚
			pstmt=connection.prepareStatement(addSQL);
			pstmt.setInt(1,dorm.getDormnum());
			pstmt.setString(2,dorm.getDormtext());
			pstmt.execute();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();//事务进行回滚，回到最初的状态
			} catch (SQLException e1) {
				System.out.println("事务提交异常");
			}
			e.printStackTrace();
			return false;
		}finally{
			MysqlOperation.close(pstmt);
			MysqlOperation.close(connection);
		}
		return true;
	}

2、删除数据

	public boolean dormdelete(String dormnum) {
		Connection connection=MysqlOperation.getConnection();
		String sql="delete from dorm where dormnum=?";
		PreparedStatement pstmt=null;
		try {
			connection.setAutoCommit(false);
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1,dormnum);
			pstmt.execute();
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		}finally{
			MysqlOperation.close(pstmt);
			MysqlOperation.close(connection);
		}
		return true;
	}

3、修改数据

	public boolean dormupdate(Dorm dori) {
		Connection connection=MysqlOperation.getConnection();
		String sql="update dorm set dormtext=? where dormnum=?";
		PreparedStatement pstmt=null;
		try {
			connection.setAutoCommit(false);
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1,dori.getDormtext());
			pstmt.setInt(2,dori.getDormnum());
			pstmt.execute();
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				System.out.println("事务异常");
			}
			return false;
		}finally{
			MysqlOperation.close(pstmt);
			MysqlOperation.close(connection);
		}
		return true;
	}

4、查询数据

public List<Dorm> buildselect(String str) {
		Connection connection=MysqlOperation.getConnection();
		String findSQL="select * from dorm where dormnum=?";
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Dorm> dorms=new ArrayList<Dorm>();
		try {
			pstmt=connection.prepareStatement(findSQL);
			pstmt.setString(1,str);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				Dorm dorm=new Dorm();
				dorm.setDormnum(rs.getInt(1));
				dorm.setDormtext(rs.getString(2));
				dorms.add(dorm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally
		{
			MysqlOperation.close(rs);
			MysqlOperation.close(pstmt);
			MysqlOperation.close(connection);
		}
		
		return dorms;
	}

5 、批量添加

public boolean dormpadd(ArrayList<Dorm> dorms) {
		Connection connection=MysqlOperation.getConnection();
		String addSQL="insert into dorm(dormnum,"+
		"dormtext) values(?,?)";
		PreparedStatement pstmt=null;
		try {
			connection.setAutoCommit(false);
			pstmt=connection.prepareStatement(addSQL);
			for(Dorm dorm:dorms){
				pstmt.setInt(1,dorm.getDormnum());
				pstmt.setString(2,dorm.getDormtext());
				pstmt.addBatch(); //添加预定义参数
			}
			pstmt.executeBatch();//批量执行预定义SQL
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				System.out.println("事务提交异常");
			}
			e.printStackTrace();
			return false;
		}finally{
			MysqlOperation.close(pstmt);
			MysqlOperation.close(connection);
		}
		return true;
	}