[大数据]三十五、java基于jdbc操作MySQL数据库之增删改方法封装

java基于jdbc操作MySQL数据库之增删改方法封装

代码

public class TestJDBC2 {

	private String url = "jdbc:mysql://ip:3306/数据库名?characterEncoding=utf-8&useSSL=false";
	private String user = "user";
	private String password = "password";
	
	static {
		//加载驱动类到DM集合
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//java程序与数据库建立连接
	public Connection getConnection() throws Exception {
		
		DriverManager.setLoginTimeout(5);
		return DriverManager.getConnection(url, user, password);
	}
	
	//非参数化sql通用增删改
	public int noParamSqlUpdate(String noParamSql) throws Exception{
		Connection con = getConnection();
		//发送sql语句
		PreparedStatement ps = con.prepareStatement(noParamSql);
		int count = ps.executeUpdate();
		
		//关闭连接
		ps.close();
		con.close();
		
		return count;
	}
	
	//参数化sql-单笔-封装的方法做不到通用,都是据业务实情来写的
	public int insertData(String paramSql,AutoLog autolog) throws Exception {
		//初始化
		Connection con = getConnection();
		//发送sql语句
		PreparedStatement ps = con.prepareStatement(paramSql);
		ps.setString(1, autolog.getTestCase());
		ps.setString(2, autolog.getReqType());
		ps.setString(3, autolog.getReqUrl());
		ps.setString(4, autolog.getReqData());
		ps.setString(5, autolog.getExpResult());
		ps.setString(6, autolog.getActResult());
		ps.setInt(7, autolog.getResult());
		ps.setString(8, autolog.getExecTime());

		int count = ps.executeUpdate();
		
		//关闭连接
		
		ps.close();
		con.close();
		
		return count;
	}
	
	//参数化sql-批量-封装的方法做不到通用,都是据业务实情来写的
	public int[] batchInsertData(String paramSql,List<AutoLog> list) throws Exception {
		//初始化
		Connection con = getConnection();
		//发送sql语句
		PreparedStatement ps = con.prepareStatement(paramSql);
		for (AutoLog autoLog : list) {
			ps.setString(1, autoLog.getTestCase());
			ps.setString(2, autoLog.getReqType());
			ps.setString(3, autoLog.getReqUrl());
			ps.setString(4, autoLog.getReqData());
			ps.setString(5, autoLog.getExpResult());
			ps.setString(6, autoLog.getActResult());
			ps.setInt(7, autoLog.getResult());
			ps.setString(8, autoLog.getExecTime());
			ps.addBatch();
		}
		int[] count = ps.executeBatch();
		
		//关闭连接
		
		ps.close();
		con.close();
		
		return count;
	}
}