本篇的学习,需要对数据库有一定的了解,这里基于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 set
while(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 set
while(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;
@Override
protected 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);
}
@Override
protected 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