java_web 学习记录(八)：jdbc + mysql

本篇的学习，需要对数据库有一定的了解，这里基于mysql进行数据库的连接操作。

JDBC，Java DataBase Connectivity，java数据库连接，可以为多种关系数据库提供统一访，是一种用于执行SQL语句的Java API。

简单地说 , JDBC 可做三件事：不数据库建立连接、发送操作数据库的语句、返回处理结果。

下面我们来学习，如何使用JDBC连接数据库Mysql.

一，添加依赖包

<!-- mysql --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.29</version></dependency>

二，添加mysql 连接配置

#数据库连接驱动db.driver=com.mysql.jdbc.Driver#数据库连接地址db.url=jdbc:mysql://localhost:3306/database?serverTimezone=UTC&characterEncoding=utf-8#数据库用户名db.username=root#数据库密码db.password=root

这里注意几点：

1，配置参数不能有空格，否则数据库连接不上

2，添加配置serverTimezone=UTC，否则会报错：

The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone.

三，在监听器中添加以上配置，初始化读取配置

package com.example.listener;import java.io.IOException;import java.io.InputStream;import java.util.Enumeration;import java.util.Properties;import javax.servlet.ServletContextEvent;import javax.servlet.ServletContextListener;import com.example.util.PropertiesUtil;/** * 监听器，初始化读取*.properties文件内容 * @author Administrator * */public class PropertiesListener implements ServletContextListener{public void contextInitialized(ServletContextEvent sce) {ClassLoader loader = this.getClass().getClassLoader();//获取upload.properties文件内容InputStream in1 = loader.getResourceAsStream("/upload.properties");if (in1 != null) {Properties prop = new Properties();try {prop.load(in1);Enumeration<?> e = prop.propertyNames();while (e.hasMoreElements()) {String key = (String) e.nextElement();String value = prop.getProperty(key);//将文件内容封装到PropertiesUtil对象中PropertiesUtil.getInstance().addProperty(key, value);}//关闭输入流in1.close();} catch (IOException e) {e.printStackTrace();}}//获取db.properties文件内容InputStream in2 = loader.getResourceAsStream("/db.properties");if (in2 != null) {Properties prop = new Properties();try {prop.load(in2);Enumeration<?> e = prop.propertyNames();while (e.hasMoreElements()) {String key = (String) e.nextElement();String value = prop.getProperty(key);//将文件内容封装到PropertiesUtil对象中PropertiesUtil.getInstance().addProperty(key, value);}//关闭输入流in2.close();} catch (IOException e) {e.printStackTrace();}}System.out.println(PropertiesUtil.getInstance().getProperties());}public void contextDestroyed(ServletContextEvent sce) {// TODO Auto-generated method stub}}

四，简单了解下数据库操作过程，方便编写工具类

1) 读取配置参数，这里使用监听器，在服务器启动时就会自动读取

2) 获取数据库连接驱动，需要用到配置参数：db.driver

3) 使用数据库连接驱动，获取数据库连接Connection,需要的参数(url,username,root)

4) 获取到数据库连接后创建执行sql语句对象，Statement 或者 PreparedStatement

5) 执行语句分为两类，query , update

如何是query方法，会返回查询的结果集，可以使用对象ResultSet接收处理

update方法则包含了insert，update，delete 三种操作，代码是一样的，只是sql语句不一样

6) 执行完成后，关闭连接，释放资源

五，根据上述步骤，我们来编写连接数据库工具类 MysqlUtil

package com.example.util;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;public class MysqlUtil {/** * 加载驱动 */static {//加载数据库驱动try {Class.forName(PropertiesUtil.getInstance().getProperty("db.driver"));} catch (ClassNotFoundException e) {e.printStackTrace();}}/** * 获取数据库连接 * @return */public static Connection getConnection () {Connection conn = null;try {String url = PropertiesUtil.getInstance().getProperty("db.url");String username = PropertiesUtil.getInstance().getProperty("db.username");String password = PropertiesUtil.getInstance().getProperty("db.password");//获取连接conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}return conn;}/** * 关闭数据库连接 * @param conn */public static void closeConnection(Connection conn){if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}/** * 关闭语句对象 * @param stmt */public static void closeStatement(Statement stmt){if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}}/** * 关闭结果集对象 * @param stmt */public static void closeResultSet (ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}}/** * 获取结果集数据集合 * @param table_name * @param sql * @return */public static List<Map<String,Object>> getColumnDatas (String sql) {List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();Connection conn = getConnection();Statement stmt = null;ResultSet rs = null;try {stmt = conn.createStatement();rs = stmt.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();int count = rsmd.getColumnCount();//这里需要使用rs.next()做判断，不然会报错：Before start of result setwhile(rs.next()){//按照数据库列顺序排列Map<String,Object> map = new LinkedHashMap<String,Object>();for (int i = 1; i <= count; i++) {String columnName = rsmd.getColumnLabel(i);Object columnData = rs.getObject(i);map.put(columnName, columnData);}datas.add(map);}} catch (SQLException e) {e.printStackTrace();} finally {closeResultSet(rs);closeStatement(stmt);closeConnection(conn);}return datas;}/** * 预编译方式获取结果集数据集合 * @param table_name * @param sql * @return */public static List<Map<String,Object>> getColumnDatas (String sql, Object... args) {List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();Connection conn = getConnection();PreparedStatement stmt = null;ResultSet rs = null;try {stmt = conn.prepareStatement(sql);          // 为pStement对象设置SQL参数值          for (int i = 0; i < args.length; i++) {          stmt.setObject(i + 1, args[i]);          }          stmt.execute();          rs = stmt.getResultSet();ResultSetMetaData rsmd = rs.getMetaData();int count = rsmd.getColumnCount();//这里需要使用rs.next()做判断，不然会报错：Before start of result setwhile(rs.next()){for (int i = 1; i <= count; i++) {String columnName = rsmd.getColumnLabel(i);Object columnData = rs.getObject(i);Map<String,Object> map = new LinkedHashMap<String,Object>();map.put(columnName, columnData);datas.add(map);}}} catch (SQLException e) {e.printStackTrace();} finally {closeResultSet(rs);closeStatement(stmt);closeConnection(conn);}return datas;}        /**     * 增删改操作     * @param sql     * @return     * @throws SQLException     */    public static int getUpdateResult(String sql) {    int result = 0;    Connection conn = getConnection();Statement stmt = null;    try {stmt = conn.createStatement();result = stmt.executeUpdate(sql);} catch (SQLException e) {e.printStackTrace();} finally {closeStatement(stmt);closeConnection(conn);}    return result;    }        /**     * 预编译方式实现增删改操作     * @param sql     * @return     * @throws SQLException     */    public static int getUpdateResult(String sql, Object... args) {    int result = 0;    Connection conn = getConnection();PreparedStatement stmt = null;    try {    stmt = conn.prepareStatement(sql);// 为pStement对象设置SQL参数值  for (int i = 0; i < args.length; i++) {  stmt.setObject(i + 1, args[i]);  }result = stmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeStatement(stmt);closeConnection(conn);}    return result;    }    }

这里说明一下：

1) Statement 和 PreparedStatement 的区别：

PreparedStatement 继承了 Statement，主要是对sql语句的操作不同

使用Statement，需要填写完整的sql语句，比如：

String name = "abc";String sql = "select * from user_info where name = "+name;

而PreparedStatement采用预编译的方式，可以使用占位符，防止sql注入

String name = "abc";String sql = "select * from user_info where name =?";PreparedStatement stmt = conn.prepareStatement(sql);stmt.setInt(1, name);

先使用占位符?代替参数值，再将参数id注入到语句中，我们上面说了可以防止sql语句注入，那么好处是什么呢？

按照上面写的sql语句，如果用户传递参数 name="'a' or 1=1"，

那么拼接的sql语句就会变成：“select * from user_info where name = 'a' or 1=1”，这样就算不知道用户名是什么，也能查出所有的数据

大家可以自行测试下。

2) 对于查询操作，返回的结果集ResultSet，必须使用rs.next()判断是否存在下一个对象，否则会报错：Before start of result set。

这里我们使用ResultSetMetaData rsmd = rs.getMetaData();获取到结果集的元数据，可以获取到列名，以及对应的数据。

我们构建一个map，用于存储获取的数据，返回给调用者。

3) 注意这里要使用 LinkedHashMap ，可以按照数据库列表顺序排序。

4) 获取列名的方法要使用rsmd.getColumnLabel(i)，而不是rsmd.getColumnName(i)，因为可以获取到别名。

5) 获取列数据，可以用列的序号获取，也可以通过列名获取，方法一样。注意列的序号是从1开始的。

6) 请注意，每个方法最后都有关闭连接，且都放在finally代码块中执行，因为数据库连接资源是有限的，用完后必须及时归还。

五，工具类都准备好了，下面我们要开始测试了，编写测试类JdbcServlet

doGet方法，我们主要测试查询操作，doPost方法则测试增删改的操作：

package com.example.servlet;import java.io.IOException;import java.io.PrintWriter;import java.util.List;import java.util.Map;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.serializer.SerializerFeature;import com.example.util.MysqlUtil;/** * 数据库连接， * 对数据进行增删改查等操作 * @author Administrator * */public class JdbcServlet extends HttpServlet {private static final long serialVersionUID = -7443125918779605180L;@Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {req.setCharacterEncoding("utf-8");resp.setCharacterEncoding("utf-8");resp.setHeader("content-type", "text/html,utf-8");PrintWriter out = resp.getWriter();//1,根据id查询数据,预编译方式Integer id = Integer.parseInt(req.getParameter("id"));String query = "select * from user_info where id=?";List<Map<String,Object>> list = MysqlUtil.getColumnDatas(query,id);String query_json = JSON.toJSONString(list);//2,查询所有数据String query_all = "select * from user_info";List<Map<String,Object>> list_all = MysqlUtil.getColumnDatas(query_all);//默认格式转换时间戳String query_json_all = JSON.toJSONString(list_all, SerializerFeature.WriteDateUseDateFormat);//3,查询user_info表的所有列名List<String> columnNames = MysqlUtil.getColumnNames(query_all);String name_json1 = JSON.toJSONString(columnNames);//4,查询结果集的列名List<String> listNames = MysqlUtil.getColumnNames(list_all);String name_json2 = JSON.toJSONString(listNames);//5,更新用户名String update = "update user_info set name='acc' where id="+id;int count = MysqlUtil.getUpdateResult(update);String msg = "";if (count == 1) {msg = "success";} else {msg = "error";}out.println("查询id："+query_json+"<br/>查询所有："+query_json_all+"<br/>查询列名1："+name_json1+"<br/>查询列名2："+name_json2+"<br/>更新id："+msg);}@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {//测试预编译方法更新数据req.setCharacterEncoding("utf-8");resp.setCharacterEncoding("utf-8");resp.setContentType("text/html");PrintWriter out = resp.getWriter();Integer id = Integer.parseInt(req.getParameter("id"));String gender = req.getParameter("gender");String name = req.getParameter("name");//更新数据String sql1 = "update user_info set name='abc' where id=?";int count1 = MysqlUtil.getUpdateResult(sql1, id);String msg1 = count1==1?"更新成功":"更新失败";//插入数据String sql2 = "insert into user_info (gender,name) values(?,?)";int count2 = MysqlUtil.getUpdateResult(sql2, gender,name);String msg2 = count2==1?"插入成功":"插入失败";//删除数据String sql3 = "delete from user_info where gender=? and name=?";int count3 = MysqlUtil.getUpdateResult(sql3, "F","ccc");String msg3 = count3==1?"删除成功":"删除失败";out.println(msg1+"\n"+msg2+"\n"+msg3);}}

六，在web.xml中配置映射：

  <!-- 测试对数据库的增删改查等操作 -->  <servlet>  <servlet-name>JdbcServlet</servlet-name>  <servlet-class>com.example.servlet.JdbcServlet</servlet-class>  </servlet>  <servlet-mapping>  <servlet-name>JdbcServlet</servlet-name>  <url-pattern>/test_curd</url-pattern>  </servlet-mapping>

七，启动测试，查看返回结果：

这里注意看代码，补充一点fastjson对时间的转换，JSON.toJSONString(list_all, SerializerFeature.WriteDateUseDateFormat);

如果不进行转换，返回到页面的就是"create_data":1492858496000

当然还有其他的转换规则，比如枚举数据，空数据等等，大家可以自行查看源码。

==========================================================================================================

大家可以看到，每个操作都有一次对数据库的连接和释放，而数据库创建连接通常需要消耗相对较大的资源，创建时间也较长。

那么有什么其他好的办法吗？

下篇：java_web 学习记录(九)：jdbcpool + datasource