JDBC
Java Database Connectivty java数据库链接
基本链接
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author mephisto
*
*/
public class Dmeo1 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 1.注册驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
// 2.建立连接
/*
* Establishing SSL connection without server's identity verification is not
* recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL
* connection must be established by default if explicit option isn't set. For
* compliance with existing applications not using SSL the
* verifyServerCertificate property is set to 'false'. You need either to
* explicitly disable SSL by setting useSSL=false, or set useSSL=true and
* provide truststore for server certificate verification.
*
* 解决方式: String url = "jdbc:mysql://localhost/test?&&useSSL=true"; url 跟上
* ?&&useSSL=true
*/
String url = "jdbc:mysql://localhost/test?&&useSSL=true";
String user = "mephisto";
String password = "M@ph1st0";
connection = DriverManager.getConnection(url, user, password);
// 3.创建statement
statement = connection.createStatement();
// 4.执行查询
String sql = "Select * from demo;";
resultSet = statement.executeQuery(sql);
// 5.逐条查询
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
System.out.println(id + " " + name + " " + age);
}
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
resultSet = null;
}
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
statement = null;
}
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connection = null;
}
}
}
}
关闭数据库类
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
public static void release(Connection con, Statement st, ResultSet rs) {
closeCon(con);
closeSt(st);
closeRs(rs);
}
private static void closeRs(ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
resultSet = null;
}
}
private static void closeSt(Statement statement) {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
statement = null;
}
}
private static void closeCon(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
connection = null;
}
}
}
操作流程
graph TD
A[注册驱动] --> B[建立连接]
B --> C[创建Statement]
C --> D[执行sql,得到ResultSet]
D --> E[得到结果]
E --> F[释放资源]
驱动防止二次注册
// Driver() 代码中有静态代码块
// 静态代码块 --> 类加载
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
关于Class.forName
在JDBC4.0以后可以不用注册,自动在META-INF/services/java.sqlDriver注册
使用单元测试流程
-
定义一个类, TestXXX, 里面定义方法 testXXX.
-
添加JUnit jar
- 右键工程 -- add Library -- JUnit --- 选择JUnit版本
-
在测试方法上添加@Test
-
@Test public void testQuery(){ .... }
-
-
光标选中方法名字,然后右键执行单元测试,或者是打开outline视图, 然后选择方法右键点击运行
基本操作
查询
// 获取连接对象
con = JDBCUtil.getCon();
// 创建statement对象
st = con.createStatement();
// 执行sql语句
String sql = "Select * from t_user where username='"+username+"' and password = '" + password +"'";
rs = st.executeQuery(sql);
if(rs.next()) {
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
插入
// 获取连接对象
con = JDBCUtil.getCon();
// 根据连接对象, 得到statement
statement = con.createStatement();
// 执行添加
String sql = "Insert into demo values (4,'mephisto',19);";
// 影响的行数, 如果大于0 代表执行成功
int result = statement.executeUpdate(sql);
if (result > 0 ) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
删除
// 获取连接对象
con = JDBCUtil.getCon();
// 根据连接对象, 得到statement
statement = con.createStatement();
// 执行删除
String sql = "delete from demo where name = 'mephisto';";
// 影响的行数, 如果大于0 代表执行成功
int result = statement.executeUpdate(sql);
if (result > 0 ) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
更新
// 获取连接对象
con = JDBCUtil.getCon();
// 根据连接对象, 得到statement
statement = con.createStatement();
// 执行更新
String sql = "update demo set name = 'lisa' where name = 'lisi';";
// 影响的行数, 如果大于0 代表执行成功
int result = statement.executeUpdate(sql);
if (result > 0 ) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
Dao模式
Data Access Object 数据访问对象
基本流程
-
新建一个dao的接口,声明数据库访问的规则
package com.mephisto.dao; public interface UserDao { /* * 查询所有 */ void findall(); } -
新建一个dao的实现类, 具体实现早前的规则
package com.mephisto.dao.impl; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import com.mephisto.dao.UserDao; import com.mephisto.util.JDBCUtil; public class UserDaoImpl implements UserDao { @Override public void findall() { Connection con = null; Statement st = null; ResultSet rs = null; try { // 获取连接对象 con = JDBCUtil.getCon(); // 创建statement对象 st = con.createStatement(); // 执行sql语句 String sql = "Select * from t_user"; rs = st.executeQuery(sql); while(rs.next()) { int id = rs.getInt("id"); String username = rs.getString("username"); String password = rs.getString("password"); System.out.println(id + " " + username +" " + password); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtil.release(con, st, rs); } } } -
直接实现
package com.mephisto.test; import org.junit.jupiter.api.Test; import com.mephisto.dao.UserDao; import com.mephisto.dao.impl.UserDaoImpl; public class TestUserDaoImpl { @Test public void testFindAll(){ UserDao dao = new UserDaoImpl(); dao.findall(); } }
PreparedStatement
Statement安全问题
-
Statement执行, 其实是拼接sql语句的, 先拼接sql语句, 然后在执行在一起
String sql = "Select * from t_user where username='"+username+"' and password = '" + password +"'";UserDao dao = new UserDaoImpl(); dao.login("admin","adm1n' or '1=1");select * from t_user where username = 'admin' and password = 'adm1n' or '1=1' -
PreparedStatement
该对象是替换statement对象,相较statement,预先处理给定的sql语句,对其执行语句进行语法检查, 在sql语句中使用?占位符来代替后需要传递进来的变量, 后面进来的变量值, 将会被看成字符串,不会产生任何的关键字
// 获取连接对象 con = JDBCUtil.getCon(); // 创建PrepareStatement对象 String sql = "select * from t_user where username = ? and password = ?"; // 预先对sql语句执行语法校验, ? 对应的内容 传递值都为字符串 PreparedStatement ps = con.prepareStatement(sql); // parameterIndex索引是从1开始 ps.setString(1, username); ps.setString(2, password); // 执行sql语句 rs = ps.executeQuery(); if(rs.next()) { System.out.println("登录成功"); }else { System.out.println("登录失败"); }
基本操作
查找
UserDaoImpl.java
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
// 获取连接对象
con = JDBCUtil.getCon();
// 创建PrepareStatement对象
String sql = "select * from t_user where username = ? and password = ?";
// 预先对sql语句执行语法校验, ? 对应的内容 传递值都为字符串
PreparedStatement ps = con.prepareStatement(sql);
// parameterIndex索引是从1开始
ps.setString(1, username);
ps.setString(2, password);
// 执行sql语句
rs = ps.executeQuery();
if(rs.next()) {
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(con, st, rs);
}
TestUserDaoImpl.java
UserDao dao = new UserDaoImpl();
// dao.login("admin","adm1n' or '1=1");
dao.login("admin","10086");
添加
UserDaoImpl.java
Connection con = null;
PreparedStatement ps = null;
try {
con = JDBCUtil.getCon();
String sql = "insert into t_user values (null,?,?);";
ps = con.prepareStatement(sql);
ps.setString(1, username);
ps.setString(2, password);
int result = ps.executeUpdate();
if(result > 0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
TestUserDaoImpl.java
UserDao dao = new UserDaoImpl();
dao.insert("mephisto", "12345");
删除
UserDaoImpl.java
try {
con = JDBCUtil.getCon();
String sql = "Delete from t_user where id = ? or username = ? or password = ?; ";
ps = con.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3, password);
int result = ps.executeUpdate();
if(result > 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(con, ps);
}
TestUserDaoImpl.java
UserDao dao = new UserDaoImpl();
dao.delete(0, "mephisto", null);
更新
UserDaoImpl.java
Connection con = null;
PreparedStatement ps = null;
try {
con = JDBCUtil.getCon();
String sql = "update t_user set username= ? where id = ?";
ps = con.prepareStatement(sql);
ps.setString(1, name);
ps.setInt(2, id);
int result = ps.executeUpdate();
if (result > 0) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.release(con, ps);
}
TestUserDaoImpl.java
UserDao dao = new UserDaoImpl();
dao.update(2, "张三");