jdbc学习----2.操作和访问数据库

操作和访问数据库

数据句连接被用于向数据库服务器发送命令和sql语句，并接受数据库服务器返回的结果，其实一个数据库连接就是一个Socket连接 在java.sql包中有3个接口分别定义了对数据库的调用的不同方式: Statement:用于执行静态SQL语句并返回所生成结果的对象 PreparedStatement:是Statement的一个子接口,表示一条预编译的sql语句,SQL语句被预编译并存储在此对象中，可以使用此对象多次高效的执行该语句 CallableStatement:用于执行SQL存储过程

PreparedStatement vs Statement

代码的可读性和可维护性PreparedStatement能最大可能提高性能 DBServer会对预编译语句提供性能优化，因为预编译语句可能会被重复调用在Statement语句中，即使是相同操作但因为数据内容不一样，所以整个语句本身不能匹配，没有魂村的意义语法检查，语义检查，翻译成二进制命令，缓存 PreparedStatement可以防止SQL注入

PrepareStatement增删改查

增加

@Test public void test1() throws Exception{ InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(is); String url = properties.getProperty("url"); String user = properties.getProperty("user"); String password = properties.getProperty("password"); Connection connection = DriverManager.getConnection(url, user, password); String sql = "insert into customers(name,email,birth) values(?,?,?)";//?是占位符 PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "阿狸"); preparedStatement.setString(2, "ali@qq.com"); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = simpleDateFormat.parse("2000-07-14"); preparedStatement.setDate(3, new java.sql.Date(date.getTime())); preparedStatement.execute(); preparedStatement.close(); connection.close(); System.out.println("执行完毕!"); }

修改

@Test public void test2() throws Exception { //1.获取数据库连接 Connection connection = JdbcUtils.getConnection(); //2.编写预编译sql语句生成ps对象 String sql = "update customers set name=? where id=?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, "皇子"); ps.setInt(2, 19); //3.执行sql语句 ps.execute(); //4.关闭连接资源 JdbcUtils.close(connection, ps); }

查询

/** * 查询 */ @Test public void test3() throws Exception { Connection connection = JdbcUtils.getConnection(); String sql = "select * from customers"; PreparedStatement ps = connection.prepareStatement(sql); ResultSet result = ps.executeQuery(); while (result.next()) { int id = result.getInt(1); String name = result.getString(2); String email = result.getString(3); Date date = new Date(result.getDate(4).getTime()); System.out.println(id); System.out.println(name); System.out.println(email); System.out.println(date); } JdbcUtils.close(connection, ps, result); }

查询优化:

/** * 查询优化 * @param sql * @return * @throws Exception */ public static Customers query(String sql) throws Exception { Connection connection = getConnection(); PreparedStatement ps = connection.prepareStatement(sql); ResultSet result = ps.executeQuery(); ResultSetMetaData metaData = result.getMetaData();//元数据 int count = metaData.getColumnCount();//列数 Customers customer = null; while(result.next()) { customer = new Customers(); for(int i = 0;i < count;i ++) { Field field = Customers.class.getDeclaredField(metaData.getColumnName(i + 1)); field.setAccessible(true); field.set(customer, result.getObject(i+1)); } } //关闭连接资源 close(connection,ps,result); return customer; }

getColumnName()方法:有别名则获取别名 getColumnLabel()方法:获取列名 ORM编程思想:

一个数据库对应一个java类表中的一条记录对应java类的一个对象表中的一个字段对应java类的一个属性

通用查询方法:

/** * 通用查询方法 */ public static <T> T queryForObject(String sql, Class<T> clazz, Object... args) throws Exception { Connection connection = getConnection(); PreparedStatement ps = connection.prepareStatement(sql); //设置占位符 for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } ResultSet result = ps.executeQuery(); ResultSetMetaData metaData = result.getMetaData(); int count = metaData.getColumnCount(); T t = null; if (result.next()) { t = clazz.getDeclaredConstructor().newInstance(); for (int i = 0; i < count; i++) { Field field = clazz.getDeclaredField(metaData.getColumnLabel(i + 1)); field.setAccessible(true); field.set(t, result.getObject(i + 1)); } } return t; }

通用查询方法:

/** * 通用查询方法 */ public static <T> List<T> queryForObjectList(String sql, Class<T> clazz, Object... args) throws Exception { Connection connection = getConnection(); PreparedStatement ps = connection.prepareStatement(sql); if(args.length > 0) { //设置占位符 for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } } ResultSet result = ps.executeQuery(); ResultSetMetaData metaData = result.getMetaData(); int count = metaData.getColumnCount(); T t = null; List<T> list = new ArrayList<>(); while (result.next()) { t = clazz.getDeclaredConstructor().newInstance(); for (int i = 0; i < count; i++) { Field field = clazz.getDeclaredField(metaData.getColumnLabel(i + 1)); field.setAccessible(true); field.set(t, result.getObject(i + 1)); } list.add(t); } return list; }

PreparedStatement的好处: 解决sql拼串，sql注入问题，可以插入blob数据 可以实现更高效的批量插入

小结

两种思想:

面向接口编程思想ORM思想 一个数据表对应一个java类表中的一条记录对应java类的一个对象表中的一个字段对应java类的一个属性

两种技术:

JDBC结果集的元数据:ResultMetaData 获取列数:getColumnCount()获取列的别名:getColumnLabel() 通过反射,创建指定类的对象,获取指定的属性并赋值

操作BLOB类型字段

mysql中,blob是一个二进制大型对象,是一个可以存储大量数据的容器,他能容纳不同大小的数据 插入blob类型的数据必须使用PreparedStatement，因为BLOB类型的数据是无法使用字符串拼接写的 mysql的四种blob类型: TinyBlob:最大255b Blob:最大65K MediumBlob:最大16M LongBlob:最大4G 插入blob数据:

@Test public void test7() throws Exception{ Connection connection = JdbcUtils.getConnection(); String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); ps.setObject(1, "张无忌"); ps.setObject(2, "zhangwuji@qq.com"); ps.setObject(3, "2000-07-14"); InputStream inputStream = new FileInputStream(new File("src/main/resources/1.png")); ps.setBlob(4, inputStream); ps.execute(); }

读取blob数据:

@Test public void test8() throws Exception{ Connection connection = JdbcUtils.getConnection(); String sql = "select * from customers where id=20"; PreparedStatement ps = connection.prepareStatement(sql); ResultSet result = ps.executeQuery(); while(result.next()) { Integer id = result.getInt(1); String name = result.getString(2); String email = result.getString(3); Date date = new Date(result.getDate(4).getTime()); Customers customer = new Customers(id,name,email,date,null); Blob blob = result.getBlob(5); InputStream is = blob.getBinaryStream(); int len = 0; byte[] data = new byte[1024]; OutputStream os = null; while((len = is.read(data)) != -1) { os = new FileOutputStream("test.png"); os.write(data, 0, len); } System.out.println(customer); is.close(); os.close(); } ps.close(); result.close(); }

如果指定了相关的Blob类型以后，还报错:xxx to large，那么在mysql的安装目录下，找到mysql.ini文件加上配置参数:max_allowed_packet=16M,修改后需要重启mysql服务才能生效.

批量数据操作

/** * 批量数据操作 */ public void batch() throws Exception{ Connection connection = JdbcUtils.getConnection(); String sql = "insert into customers(name,email,birth) values(?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); for(int i = 0;i < 1000;i ++) { ps.setString(1, "张三"+i); ps.setString(2, "zhangsan"+i+"@qq.com"); ps.setString(3, "2000-07-14"); ps.execute(); } }

批处理体验

/** * 批量数据操作 * 使用批处理之前:插入500条记录耗时68秒 * 使用批处理之后:插入500条记录250毫秒 */ @Test public void batch() throws Exception{ Connection connection = JdbcUtils.getConnection(); String sql = "insert into customers(name,email,birth) values(?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); /** * 批处理 * mysql服务器默认是关闭批处理的,开启需要在url后面加上rewriteBatchedStatements=true */ long start = System.currentTimeMillis(); for(int i = 0;i < 501;i ++) { ps.setString(1, "李四"+i); ps.setString(2, "zhangsan"+i+"@qq.com"); ps.setString(3, "2000-07-14"); ps.addBatch(); if(i % 500 == 0) { ps.executeBatch(); ps.clearBatch(); } } long end = System.currentTimeMillis(); System.out.println("花费的时间:"+(end-start)+"毫秒"); connection.close(); ps.close(); }

结果: 进一步优化插入数据:

/** * 批量数据操作 * 使用批处理之前:插入500条记录耗时68秒 * 使用批处理之后:插入500条记录耗时250毫秒 * 设置不自动提交:插入500条记录耗时128毫秒 */ @Test public void batch() throws Exception{ Connection connection = JdbcUtils.getConnection(); //设置不允许自动提交 connection.setAutoCommit(false); String sql = "insert into customers(name,email,birth) values(?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); /** * 批处理 * mysql服务器默认是关闭批处理的,开启需要在url后面加上rewriteBatchedStatements=true */ long start = System.currentTimeMillis(); for(int i = 0;i < 501;i ++) { ps.setString(1, "李四"+i); ps.setString(2, "zhangsan"+i+"@qq.com"); ps.setString(3, "2000-07-14"); ps.addBatch(); if(i % 500 == 0) { ps.executeBatch(); ps.clearBatch(); } } //提交数据 connection.commit(); long end = System.currentTimeMillis(); System.out.println("花费的时间:"+(end-start)+"毫秒"); connection.close(); ps.close(); }