数据库编程

2020-04-09

说到java的数据库编程，就要先要了解一下何为JDBC

JDBC(Java Data Base Connectivity)，是用于在Java语言编程中与数据库连接的API。
JDBC是一个规范，它提供了一整套接口，允许以一种可移植的访问底层数据库API。使用JDBC驱动程序来访问数据库，并用于存储数据到数据库中.

下图表现了应用程序，JDBC API，数据库驱动及数据库之间的关系。

JDBC屏蔽了底层不同数据库的类型，使得应用程序通过JDBC接口访问不同数据库。

连接数据库的步骤如下

获取驱动程序Jar文件，并放置到项目的类路径中；
注册驱动（一次即可）
建立连接（connection）
创建执行sql的语句（Statement）
执行sql语句
处理结果
释放资源

然后来详细介绍一下数据库编程的原理

1 加载及注册JDBC驱动程序

首先需要添加mysql驱动，将com.mysql.jdbc.Driver下载后，添加到WebContent / web-INF/ lib 下面。然后加载和注册JDBC驱动。

1 2	Class.forName("com.mysql.jdbc.Driver"); Class.forName("com.mysql.jdbc.Driver").newInstance();

2 建立连接对象

建立连接对象con ，从而获取数据库连接

1 2	String url=“JDBC URL"; Connection con = DriverManager.getConnection(url);

2.1 了解 JDBC URL

JDBC URL 是定义驱动程序与数据源之间的连接。

MySQL的JDBC URL格式：

jdbc:mysql//[hostname][:port]/[dbname][?param1=value1][&param2=value2]….

例如加上数据库用户名和密码

jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password

格式中常见的param含义如下：

param	含义
user	用户名
password	密码
autoReconnect	联机失败，是否重新联机（true/false）
maxReconnect	尝试重新联机次数
initialTimeout	尝试重新联机间隔
maxRows	传回最大行数
useUnicode	是否使用Unicode字体编码（true/false）
characterEncoding	何种编码（GB2312/UTF-8/…）
relaxAutocommit	是否自动提交（true/false）
capitalizeTypeNames	数据定义的名称以大写表示

3 创建数据库发送SQL语句

getConnection()方法只是获取与数据库的连接，而要执行SQL语句首先要获得Statement类对象。

Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句。Statement对象，用于执行不带参数的简单SQL语句。

通过连接数据库对象 con 的createStatement()方法可获得Statement对象。

1 2	Statement stmt = con.createStatement()； //建立SQL陈述式对象 rs = st.executeQuery(sql);//结果集

3.1 预处理语句PreparedStatement

Connection对象的createStatement()方法，是向数据库发送一个SQL语句，数据库中的SQL解释器负责把SQL语句生成底层的内部命令，然后执行该命令，完成相关的数据操作。如果不断地向数据库提交SQL语句，肯定会增加数据库中SQL解释器的负担，影响执行的速度。

那么preparedStatement方法呢？

PreparedStatement stmt = conn.prepareStatement("insert into test(id,name)values(?,?)");
stmt.setInt(1,id); // 预处理添加数据
stmt.setString(2,name);
stmt.executeUpdate();

Connection对象的preparedStatement(String sql)方法对SQL语句进行预处理，生成数据库底层的内部命令,并将该命令封装在PreparedStatement对象中，通过调用该对象的相应方法执行底层数据库命令,就不需要解释器频繁操作。

同时PreparedStatement还可以有效的解决SQL注入问题

首先需要了解一下SQL注入

SQL注入(sql inject)，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。

在SQL中包含特殊字符或SQL关键字（如：‘or 1）时statement将出现严重和结果（异常或结果错误），例如下面这个例子。

1	sql = "SELECT USERNAME,PASSWORD FROM USER WHERE USERNAME='" + username + "' AND PASSWORD='" + password + "'";

如果某人输入一个数据库存在的username “lili”，密码是 aaa’ or ‘1’=’1这样的那处理登录的SQL语句就会变成这样

1	SELECT USERNAME,PASSWORD FROM USER WHERE USERNAME='lili' AND PASSWORD='aaa' or '1'='1';

上面的SQL语句就会执行成功，用户数据就会泄露。SQL注入可以通过PreparedStatement来解决。

4 执行SQL语句处理结果集ResultSet

有了Statement对象以后，可调用相应的方法实现对数据库的查询和修改，并将查询的结果集存放在ResultSet类的对象中。

如下是对数据库进行查询

1 2	String query = "select * from test";//查询数据库 ResultSet rs=stmt.executeQuery(query);//获得结果集

ResultSet类的next()方法的返回值是boolean类型的数据，当游标移动到最后一行之后会返回false。

while(rs.next()) {   
    String name = rs.getString("name");//从数据库中获取数据
    String id = rs.getString("id");
    System.out.print(" 姓名：" + name);
    System.out.print(" ID：" + id);
}

4.1 execute、executeQuery和executeUpdate之间的区别

JDBC中Statement 接口提供了三种执行 SQL 语句的方法：executeQuery、executeUpdate 和 execute。使用哪一个方法由 SQL 语句所产生的内容决定。

1）executeQuery

用于产生单个结果集（ResultSet）的语句，例如 SELECT 语句，执行后返回代表查询结果的ResultSet对象。

2）executeUpdate

用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句。

当executeUpdate(sql)是INSERT、UPDATE 或 DELETE 语句时，返回的是受影响的行数（即更新的行数）。比如删除用户表中的一行数据据，返回值是1。
当executeUpdate(sql)是CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值是零。

3）execute

可用于执行任何SQL语句，返回一个boolean值，表明执行该SQL语句是否返回了ResultSet。如果执行后第一个结果是ResultSet，则返回true，否则返回false。

1	boolean rs = st.execute(sql);

4.2 conn.setAutoCommit开启事物

setAutoCommit(boolean autoCommit) 默认autoCommit是true，即自动提交。当每执行一个update ,delete或者insert的时候都会自动提交到数据库，无法回滚事务。

autoCommit的取值

true：sql命令的提交（commit）由驱动程序负责
false：sql命令的提交由应用程序负责，程序必须调用commit或者rollback方法

那什么时候需要设置setAutoCommit（false）

当多个sql语句被视为一个整体，要么都执行，要不一个也不执行，

例如下面的第一条sql语句是根据用户的id 和密码查找用户，第二条语句是删除刚刚查找的用户的信息，这两条语句在时间上具有顺序连接性。

conn = DatabaseUtil.getInstance().getConnection();
//开启事物
conn.setAutoCommit(false);
st = conn.createStatement();
//根据id和name查找某一用户
String query = "select * from reader where reader_id=" + id + " and reader_name='" + name + "'";
//执行查询
rs = st.executeQuery(query);
if (rs.next()) {
    //删除刚才查找的读者的信息
    String sql = "delete from reader where reader_id=" + id;
    int row = st.executeUpdate(sql);
    if (row == 1) {
        tag = true;
    }
} else {
    System.out.println("--ReaderDAO--,--deleteReaderById()--,id和name不匹配");
    tag = false;
}
conn.commit();//提交书事物

4.3 了解resultSetType 和 resultSetConcuttency

Statement对象不同 resultSetType 使得 ResultSet对象中的光标移动方向不同，在从结果集中读取济洛路时，第一种对于访问过的记录就自动释放了内存，后两者访问过的所有记录都保留在内存中不能释放。

resultSetType	含义
TYPE_FORWARD_ONLY	只允许结果集的游标向下移动，只能使用next（）方法。（默认类型）
TYPE_SCROLL_SENSITIVE	可以上下移动，可以取得改变后的值。
TYPE_SCROLL_INSENSITIVE	可以上下移动。

关于resultSetConcuttency

类型	含义
CONCUR_READ_ONLY	只读
CONCUR_UPDATABLE	ResultSet对象可以执行数据库的新增、修改、和移除

6 释放资源

调用.close方法释放资源

释放ResultSet，Statement，Connection

Connection的使用原则是尽量晚创建，尽可能早释放，因为数据库的连接很有限，如果不及时释放将导致系统崩溃

7 总结

使用 Class.forName() 方法可以将驱动程序加载到 Java 解释器中

使用 DriverManager 类的 getConnection() 方法和 Connection 对象的 createStatement() 方法可建立连接

最后，使用 executeQuery() 或 executeUpdate() 方法通过 Statement 实例构建并执行 SQL 语句

PreparedStatement 接口允许创建预编译的 SQL 语句，并使得在后续阶段可以指定语句的参数

二、实际项目中数据库编程

在实际项目中，通常把对数据库的链接和关闭方法封装在一个工具类中。

如下，是采用单一实例化完成对数据库的连接，同时提供关闭数据库方法

public class DatabaseUtil {

 // ?useUnicode=true&characterEncoding=UTF-8是解决后面存取数据库时中文乱码问题,
// &serverTimezone=GMT解决数据库连接出现时区错误问题
	private String url = "jdbc:mysql://localhost:3306/librarysystem?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8";
	private String userName = "root";
	private String password = "*****";
	private static DatabaseUtil instance = null;//单一实例模式

	static {
		// 静态模块注册，保证只注册一次
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("--DatabaseUtil--,Cannot find Class : MySQL Jdbc Driver");
		}

	}

	private DatabaseUtil() {
	}

	/**
	 * 通过该方法获取对象，保证单一实例
	 * 
	 * @return DatabaseUtil
	 */
	public static DatabaseUtil getInstance() {
		// 加锁，防止并发
		synchronized (DatabaseUtil.class) {
			if (instance == null) {
				instance = new DatabaseUtil();
			}
		}
		return instance;
	}

	/**
	 *  获取数据库连接
	 * 
	 * @return Connection
	 */
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, userName, password);
	}

	/**
	 * 关闭当前数据库的连接
	 * 
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public void closeConnection(Connection conn, Statement st, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				System.out.println("数据库链接已关闭！");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

添加、修改、删除记录

可以通过SQL语句对数据执行添加、修改和删除操作。可通过PreparedStatement类的指定参数动态地对数据表中原有数据进行修改操作，并通过executeUpdate()方法执行更新语句。

import java.sql.*;
 
public class Test { // 创建类
	static Connection con = null;; // 声明Connection对象
	static 	Statement st = null;; // 声明PreparedStatement对象
	static ResultSet  = null;// 声明ResultSet对象
 
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql:"
					+ "//127.0.0.1:3306/test", "root", "123456");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
 
	public static void main(String[] args) {
		Renewal c = new Renewal(); // 创建本类对象
		con = c.getConnection(); // 调用连接数据库方法
		try {
			sql = con.prepareStatement("select * from tb_stu"); // 查询数据库
			res = sql.executeQuery(); // 执行SQL语句
			System.out.println("执行增加、修改、删除前数据:");
			while (res.next()) {
				String id = res.getString(1);
				String name = res.getString("name");
				String sex = res.getString("sex");
				String birthday = res.getString("birthday"); // 遍历查询结果集
				System.out.print("编号：" + id);
				System.out.print(" 姓名：" + name);
				System.out.print(" 性别:" + sex);
				System.out.println(" 生日：" + birthday);
			}
			sql = con.prepareStatement("insert into tb_stu(name,sex,birthday) values(?,?,?)");
			sql.setString(1, "张一"); // 预处理添加数据
			sql.setString(2, "女");
			sql.setString(3, "2012-12-1");
			sql.executeUpdate();
			sql = con.prepareStatement("update tb_stu set birthday "
					+ "= ? where id = ? ");
			sql.setString(1, "2012-12-02"); // 更新数据
			sql.setInt(2, 1); // 更新数据
			sql.executeUpdate();
			Statement stmt = con.createStatement();
			stmt.executeUpdate("delete from tb_stu where id = 1");
			// 查询修改数据后的tb_stu表中数据
			sql = con.prepareStatement("select * from tb_stu");
			res = sql.executeQuery(); // 执行SQL语句
			System.out.println("执行增加、修改、删除后的数据:");
			while (res.next()) {
				String id = res.getString(1);
				String name = res.getString("name");
				String sex = res.getString("sex");
				String birthday = res.getString("birthday");
				System.out.print("编号：" + id);
				System.out.print(" 姓名：" + name);
				System.out.print(" 性别:" + sex);
				System.out.println(" 生日：" + birthday);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}