Java-JDBC
-
数据库访问技术简介：
-
-
JDBC的工作原理
JDBC访问数据库步骤
步骤1：加载驱动
-
使用Class类的forName方法，将驱动程序类加载到JVM（Java虚拟机）中；
private static String driverName= "com.mysql.cj.jdbc.Driver";//mysql 8.0 Class.forName(driver);//加载驱动
步骤2：获取数据库连接
-
成功加载驱动后，必须使用DriverManager类的静态方法getConnection来获得连接对象；
- jdbc:mysql://localhost:3306/数据库名称?参数名称=参数值&参数名称=参数值
-
String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";`
-
URL参数
-
useUnicode：是否使用Unicode字符集，如果参数characterEncoding设置为gb2312或gbk，本参数值必须设置为true。
-
characterEncoding：当useUnicode设置为true时，指定字符编码。比如可设置为gb2312或gbk。
-
useSSL：MySQL在高版本需要指明是否进行SSL连接，在mysql连接字符串url中加入ssl=true或者false。
-
serverTimezone：设置时区 例如 serverTimezone=UTC（统一标准世界时间）或serverTimezone=Asia/Shanghai（中国时区）
-
步骤3：创建Statement执行SQL语句
-
通过Connection对象创建
-
用于执行SQL语句
步骤4：处理ResultSet结果集
-
用于存储查询结果
-
只在执行select语句时返回
-
步骤5：释放资源 close();
案例
User定义
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'jim', '11111');
INSERT INTO `user` VALUES ('2', 'tom', '22222');
INSERT INTO `user` VALUES ('3', 'lucy', '3333');
INSERT INTO `user` VALUES ('4', 'laowang', '4444');
INSERT INTO `user` VALUES ('5', 'laoli', '5555');使用Statement查询User
引入MySql驱动jar
user表的实体类
public class User {
private Long id;//主键
private String username;//用户名
private String password;//密码
public User(String username, String password) {
this.username = username;
this.password = password;
}
public Long getId() {return id;}
public void setId(Long id) {this.id = id; }
public String getUsername() {return username; }
public void setUsername(String username) {this.username = username; }
public String getPassword() {return password; }
public void setPassword(String password) {this.password = password;
}
@Override
public String toString() {
return "User{" +"id=" + id +", username='" + username + '\'' +", password='" + password + '\'' + '}';
}
}使用Statement查询User
import java.sql.*;
import java.util.Scanner;
public class JdbcTest {
public static void main(String[] args) {
login();
}
//模拟登陆
private static void login() {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String username = sc.nextLine();
System.out.println("请输入密码：");
String password = sc.nextLine();
User user = query(username, password);
if (user != null) {
System.out.println("欢迎" + username + "登陆成功");
} else {
System.out.println("用户名或密码不正确！");
}
}
private static User query(String username, String password) {
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
//1、通过反射，加载与注册驱动类
Class.forName("com.mysql.cj.jdbc.Driver");/*加载mysql8.0+的驱动*/
/*mysql5.0+的驱动 : Class.forName("com.mysql.jdbc.Driver");*/
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
conn = DriverManager.getConnection(url, "root", "123456");
/*DriverManager.getConnection();*/
//3、创建Statement对象并执行 执行sql返回ResultSet
statement = conn.createStatement();
String sql = "select * from `user` where username='" + username + "' and `password`='" + password + "'";
rs = statement.executeQuery(sql);
//4、处理结果集
User user = null;
/*boolean next()
将光标从当前位置向前移动一行。 ResultSet光标最初位于第一行之前; 第一次调用方法next使第一行成为当前行; 第二个调用使第二行成为当前行，依此类推。
当调用next方法返回false时，光标位于最后一行之后。
如果当前行的输入流已打开，则对方法next的调用将隐式关闭它。 当读取新行时， ResultSet对象的警告链将被清除。
结果 true如果新的当前行有效; false如果没有更多的行
*/
if (rs.next()) {
/*Object getObject(int columnIndex)
获取此的当前行中指定列的值 ResultSet作为对象
参数 columnIndex - 第一列是1，第二列是2，...
结果 一个 java.lang.Object持有列值
*/
Long id = (Long) rs.getObject(1);
String username1 = (String) rs.getObject(2);
String password1 = (String) rs.getObject(3);
user = new User(id, username1, password1);
}
return user;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//5、关闭资源
finally {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (statement != null) {
statement.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
SQL 注入（SQL Injection）是发生在 Web 程序中数据库层的安全漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作。
简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。
statement 只能拼接sql语句，可能会出现sql注入的风险
例如：sql注入 用户名输入 ' or 1=1 # 密码随便输入，就会查出数据），不推荐用。
JDBC的PreparedStatement
-
PreparedStatement的概述：
- 继承自Statement接口
- 能够对SQL语句进行预编译
- PreparedStatement的优点
-
- 提高SQL语句执行效率
- 提高安全性
- 注意：
-
- SQL语句使用“ ？”作为数据占位符
- 在创建时对SQL语句进行预编译
- 使用setXxx()方法设置数据
使用PreparedStatement查询
private static User query(String username, String password) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1、通过反射，加载与注册驱动类
Class.forName("com.mysql.cj.jdbc.Driver");/*加载mysql8.0+的驱动*/
//2、获取连接
String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
conn = DriverManager.getConnection(url, "root", "123456");
//3、获取PreparedStatement对象 并执行sql返回ResultSet
String sql = "select * from `user` where username= ? and `password` = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1, username);
ps.setObject(2, password);
rs = ps.executeQuery();
//4、处理结果集
User user = null;
if (rs.next()) {
Long id = (Long) rs.getObject(1);
String username1 = (String) rs.getObject(2);
String password1 = (String) rs.getObject(3);
user = new User(id, username1, password1);
}
return user;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//5、关闭资源
finally {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (ps != null) {
ps.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
解决SQL注入问题
新增
/**
* 新增
*
* @throws ClassNotFoundException
* @throws SQLException
*/
private static void insert() throws ClassNotFoundException, SQLException {
//1、加载jdbc驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、获取连接
//在mysql8.0的数据库 serverTimezone=Asia/shanghai是必须的参数
String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
Connection conn = DriverManager.getConnection(url, "root", "123456");
//3、获取PreparedStatement对象
//注意点：sql语句中使用？作为参数的占位符
String sql = "insert into `user`(username,password) values(?,?)";
PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//把问号替换成参数
ps.setObject(1, "jim");
ps.setObject(2, 30);
//4、执行sql，新增 要使用executeUpdate()
ps.executeUpdate();
// 执行此 Statement 对象而创建的所有自动生成的键
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
// 指定返回生成的主键
Object id = rs.getObject(1);
System.out.println("新增的主键为："+id);
}
//5、关闭资源
ps.close();
conn.close();
}修改和删除
/**
* 更新
*
* @throws ClassNotFoundException
* @throws SQLException
*/
private static void update() throws ClassNotFoundException, SQLException {
//1、加载jdbc驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、获取连接
//在mysql8.0的数据库 serverTimezone=Asia/shanghai是必须的参数
String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
Connection conn = DriverManager.getConnection(url, "root", "123456");
//3、获取PreparedStatement对象
//注意点：sql语句中使用？作为参数的占位符
String sql = "update user set password=? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
//把问号替换成参数
ps.setObject(1, "888");
ps.setObject(2, 1);
//4、执行sql，更新或者删除 要使用executeUpdate()，处理返回值，返回值代表的含义：影响的行数
int result = ps.executeUpdate();
if (result > 0) {
System.out.println("更新密码成功");
} else {
System.out.println("更新密码失败");
}
//5、关闭资源
ps.close();
conn.close();
}模糊查询
private static List<User> queryForLike(String usernameLike) {
//假如：usernameLike 为a
//sql语句
String sql = "select * from `user` where username like ?";
//参数
Object[] params = {"%" + usernameLike + "%"};
List<User> users = new ArrayList<>();
try {
List<Map<String, Object>> datas = DbUtils.selectList(sql, params);
for (Map<String, Object> data : datas) {
Long id = (Long) data.get("id");
String username = (String) data.get("username");
String password = (String) data.get("password");
User user = new User(id, username, password);
users.add(user);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return users;
}分页查询
/**
* 分页查询
*
* @param pageNum 页码
* @param pageSize 每页数量
* @return
*/
private static List<User> queryPage1(int pageNum, int pageSize) {
List<User> users = new ArrayList<>();
String sql = "select * from `user` order by id asc limit ?,?";
Object[] params = {(pageNum - 1) * pageSize, pageSize};
try {
List<Map<String, Object>> datas = DbUtils.selectList(sql, params);
for (Map<String, Object> data : datas) {
Long id = (Long) data.get("id");
String username = (String) data.get("username");
String password = (String) data.get("password");
User user = new User(id, username, password);
users.add(user);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return users;
}封装DbUtils工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
public class DbUtils {
private static String driverName;
private static String url;
private static String username;
private static String password;
static {
InputStream inputStream = null;
try {
//创建db.properties的流
inputStream = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
//创建Properties对象
Properties p = new Properties();
//把数据流读入Properties对象中
p.load(inputStream);
//从Properties对象中获取配置数据
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
driverName = p.getProperty("driverName");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* insert语句使用，返回新增数据的自增主键。
*
* @param sql
* @return
*/
public static Object insert(Connection connection, String sql, Object[] params) throws SQLException {
PreparedStatement ps = null;
Object id = null;
try {
//创建PreparedStatement
ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//设置参数
setPreparedStatementParam(ps, params);
//执行sql
ps.executeUpdate();
// 执行此 Statement 对象而创建的所有自动生成的键
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
// 指定返回生成的主键
id = rs.getObject(1);
}
} finally {
close(ps);
}
return id;
}
/**
* insert语句使用，返回新增数据的自增主键。
*
* @param sql
* @return
*/
public static Object insert(String sql, Object[] params) throws SQLException, ClassNotFoundException {
Connection conn = null;
Object id;
try {
//创建连接
conn = getConnection();
id = insert(conn, sql, params);
} finally {
close(conn);
}
return id;
}
/**
* 更新、删除
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static boolean update(Connection connection, String sql, Object[] params) throws SQLException {
PreparedStatement ps = null;
try {
//步骤2：设置SQL语句以及对应的参数
ps = connection.prepareStatement(sql);
setPreparedStatementParam(ps, params);
//步骤3：执行update
int result = ps.executeUpdate();
//返回执行的结果
return result > 0 ? true : false;
} finally {
//步骤4：关闭资源
close(ps);
}
}
/**
* 更新、删除
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static boolean update(String sql, Object[] params) throws SQLException, ClassNotFoundException {
Connection connection = null;
try {
//步骤1：获取链接
connection = getConnection();
return update(connection, sql, params);
} finally {
//步骤2：关闭连接资源
close(connection);
}
}
/**
* 查询一个
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static Map<String, Object> selectOne(Connection connection, String sql, Object[] params) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
//步骤2：设置SQL语句以及对应的参数
ps = connection.prepareStatement(sql);
setPreparedStatementParam(ps, params);
//步骤3：执行查询，把查询结果的列作为key，列对应的值作为value，保存到Map中
rs = ps.executeQuery();
if (rs.next()) {
return getResultMap(rs);
}
} finally {
//步骤4：关闭资源
close(rs, ps);
}
return null;
}
/**
* 获取ResultMap
*
* @param rs
* @return
* @throws SQLException
*/
private static Map<String, Object> getResultMap(ResultSet rs) throws SQLException {
//获取到result的元数据，包含了列的信息
ResultSetMetaData metaData = rs.getMetaData();
//获取到当前表的所有的列的列数
int columnCount = metaData.getColumnCount();
//存储数据库列与值的map
Map<String, Object> map = new HashMap<>();
//根据列的数量，获取到每一个列的列名以及对应的值
for (int i = 0; i < columnCount; i++) {
//能够获取到每一个列的名称，参数是每个列的序号值
String columnLabel = metaData.getColumnLabel(i + 1);
Object columnValue = rs.getObject(columnLabel);
map.put(columnLabel, columnValue);
}
return map;
}
/**
* 查询一个
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static Map<String, Object> selectOne(String sql, Object[] params) throws SQLException, ClassNotFoundException {
Connection connection = null;
try {
//步骤1：获取链接
connection = getConnection();
return selectOne(connection, sql, params);
} finally {
//步骤4：关闭资源
close(connection);
}
}
/**
* 查询集合
*
* @param sql
* @param params
* @return
*/
public static List<Map<String, Object>> selectList(Connection connection, String sql, Object[] params) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
List<Map<String, Object>> list;
try {
//步骤2：设置SQL语句以及对应的参数
ps = connection.prepareStatement(sql);
setPreparedStatementParam(ps, params);
//步骤3：执行查询，把查询结果的列作为key，列对应的值作为value，保存到Map中
rs = ps.executeQuery();
list = new ArrayList<>();
while (rs.next()) {
list.add(getResultMap(rs));
}
} finally {
//步骤4：关闭资源
close(rs, ps);
}
return list;
}
/**
* 查询集合
*
* @param sql
* @param params
* @return
*/
public static List<Map<String, Object>> selectList(String sql, Object[] params) throws SQLException, ClassNotFoundException {
Connection connection = null;
try {
connection = getConnection();
return selectList(connection, sql, params);
} finally {
close(connection);
}
}
/**
* 设置参数
*
* @param ps
* @param params
* @throws SQLException
*/
private static void setPreparedStatementParam(PreparedStatement ps, Object[] params) throws SQLException {
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
}
/**
* 获取连接
*
* @return
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//加载数据库驱动
Class.forName(driverName);
return DriverManager.getConnection(url, username, password);
}
//开启事务
public static void beginTransaction(Connection conn) throws SQLException {
conn.setAutoCommit(false);
}
//提交事务
public static void commit(Connection conn) throws SQLException {
conn.commit();
}
//回滚事务
public static void rollback(Connection conn) throws SQLException {
conn.rollback();
}
public static void close(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放PreparedStatement,Connection
*/
private static void close(PreparedStatement ps, Connection connection) {
close(ps);
close(connection);
}
/**
* 释放ResultSet,PreparedStatement
*/
private static void close(ResultSet rs, PreparedStatement ps) {
close(rs);
close(ps);
}
/**
* 释放ResultSet,PreparedStatement,Connection
*/
private static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
close(rs);
close(ps);
close(conn);
}
private static void close(Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void close(PreparedStatement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}调用存储过程
-
调用存储过程的步骤
(1)prepareCall(sql)：创建执行存储过程的callableStatement对象。
(2)CallableStatement：继承自PreparedStatement接口，由方法prepareCall创建，用于调用存储过程。
(3)执行：和其它对象使用的方法一样 eg:cs.executeUpdate()
(4)调用存储过程的sql: String sql = “{call 存储过程名字(参数1,参数2....)}”
-
案例
-
定义存储过程
/* -- 存储过程：分页查询用户列表 -- 输入参数：page_index：当前页码 -- 输入参数：page_size：分页大小 -- 输出参数：total_count：数据总数 -- 输出参数：total_page：总页数 */ DROP PROCEDURE IF EXISTS proc_search_user; CREATE PROCEDURE proc_search_user(IN page_index INT,IN page_size INT, OUT total_count INT, OUT total_page INT) BEGIN DECLARE begin_no INT; SET begin_no = (page_index-1)*page_size; -- 分页查询列表 SELECT * FROM `user` ORDER BY id ASC LIMIT begin_no,page_size; -- 计算数据总数 SELECT COUNT(1) INTO total_count FROM `user`; -- 计算总页数 SET total_page = FLOOR((total_count + page_size - 1) / page_size); END; -
Navicat工具调用存储过程
CALL proc_search_user(1,2,@total_count,@total_page); select @total_count,@total_page; -
JDBC调用存储过程
/** * 调用存储过程 * * @throws ClassNotFoundException * @throws SQLException */ private static void selectPro() throws ClassNotFoundException, SQLException { //1、加载jdbc驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2、获取连接 //在mysql8.0的数据库 serverTimezone=Asia/shanghai是必须的参数 String url = "jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai"; Connection conn = DriverManager.getConnection(url, "root", "123456"); //3、创建CallableStatement对象 CallableStatement callStatement = conn.prepareCall("{CALL proc_search_user(?,?,?,?)}"); // 设置输入参数 callStatement.setInt(1, 1); // 查询第1页数据 callStatement.setInt(2, 2); // 每页2条数据 // 注册输出参数 callStatement.registerOutParameter(3, Types.INTEGER); callStatement.registerOutParameter(4, Types.INTEGER); // 执行调用存储过程，并获取结果集 ResultSet rs = callStatement.executeQuery(); //4、执行sql返回ResultSet ResultSet rs = ps.executeQuery(); while (rs.next()) { int id = (Integer) rs.getObject(1); String username1 = (String) rs.getObject(2); String password1 = (String) rs.getObject(3); User user = new User(); user.setId(id); user.setUsername(username1); user.setPassword(password1); System.out.println(user); } //5、关闭资源 rs.close(); ps.close(); conn.close(); }
-
