MySQL函数和JDBC的使用教程
MySql函数
环境准备
-- 用户表
CREATE TABLE t_user (
id int primary key AUTO_INCREMENT,
uname varchar(40) DEFAULT NULL,
age int DEFAULT NULL,
sex int DEFAULT NULL
);
insert into t_user values (null,'zs',18,1);
insert into t_user values (null,'ls',20,0);
insert into t_user values (null,'ww',23,1);
insert into t_user values (null,'zl',24,1);
insert into t_user values (null,'lq',15,0);
insert into t_user values (null,'hh',12,0);
insert into t_user values (null,'wzx',60,null);
insert into t_user values (null,'lb',null,null);
1if函数
1.1 if函数
1.1.1 语法
if(expr1,expr2,expr3)
说明: 如果 expr1 是TRUE,则 IF()的返回值为expr2; 否则返回值则为 expr3。if() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
1.1.2 示例
练习1:获取用户的姓名、性别,如果性别为1则显示1,否则显示0;要求使用if函数查询:
SELECT uname, IF(sex, 1, 0) FROM t_user;
1.2 ifnull函数
1.2.1 语法
ifnull(expr1,expr2)
说明:假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。ifnull()的返回值是数字或是字符串,具体情况取决于其所使用的语境。
1.2.2 示例
练习1:获取用户的姓名、性别,如果性别为null则显示为1;要求使用ifnull函数查询:
SELECT uname, IFNULL(sex, 1) FROM t_user;
2字符串函数
2.1 字符串连接函数
字符串连接函数主要有2个:
函数或操作符 | 描述 |
---|---|
concat(str1, str2, …) | 字符串连接函数,可以将多个字符串进行连接 |
**concat_ws(**separator, str1, str2, …) | 可以指定间隔符将多个字符串进行连接; |
练习1:使用concat函数显示出 你好,uname 的结果
SELECT CONCAT('你好,' , uname) FROM t_user;
练习2:使用concat_ws函数显示出 你好,uname 的结果
SELECT CONCAT_WS(',', '你好', uname) FROM t_user;
2.2字符串大小写处理函数
字符串大小写处理函数主要有2个:
函数或操作符 | 描述 |
---|---|
upper(str) | 得到str的大写形式 |
lower(str) | 得到str的小写形式 |
练习1: 将字符串 hello 转换为大写显示
SELECT UPPER('hello'); -- HELLO
练习2:将字符串 heLLo 转换为小写显示
SELECT LOWER('heLLo'); -- hello
2.3 移除空格函数
可以对字符串进行按长度填充满、也可以移除空格符
函数或操作符 | 描述 |
---|---|
trim(str) | 将str两边的空白符移除 |
练习1: 将用户id位8的用户的姓名的两边空白符移除
-- 表中数据是:' lb ', 使用trim后是: 'lb'
SELECT TRIM(uname) FROM t_user WHERE id = 8;
2.4子串函数
字符串也可以按条件进行截取,主要有以下可以截取子串的函数;
函数或操作符 | 描述 |
---|---|
substr()、substring() | 获取子串: 1:substr(str, pos) 、substring(str, pos); 2:substr(str, pos, len)、substring(str, pos, len) |
练习1:获取 hello,world 从第二个字符开始的完整子串
SELECT SUBSTR("hello,world", 2); -- ello,world
练习2:获取 hello,world 从第二个字符开始但是长度为4的子串
SELECT SUBSTR("hello,world", 2, 4); -- ello
3时间日期函数
mysql提供了一些用于获取特定时间的函数:
函数或操作符 | 描述 |
---|---|
current_date() | 获取当前日期,如 2019-10-18 |
current_time() | 获取当前时:分:秒,如:15:36:11 |
now() | 获取当前的日期和时间,如:2019-10-18 15:37:17 |
练习1:获取当前的日期
select cruuent_date();
练习2: 获取当前的时间(仅仅需要时分秒)
select current_time();
练习3: 获取当前时间(包含年月日时分秒)
select now();
4数值函数
常见的数值相关函数如下表:
函数或操作符 | 描述 |
---|---|
abs(x) | 获取数值x的绝对值 |
ceil(x) | 向上取整,获取不小于x的整数值 |
floor(x) | 向下取整,获取不大于x的整数值 |
pow(x, y) | 获取x的y次幂 |
rand() | 获取一个0-1之间的随机浮点数 |
练习1: 获取 -12 的绝对值
select abs(-12);
练习2: 将 -11.2 向上取整
select ceil(-11.2);
练习3: 将 1.6 向下取整
select floor(1.6);
练习4: 获得2的32次幂的值
select pow(2, 32);
练习5: 获得一个在0-100之间的随机数
select rand()*100;
JDBC API详解
Drivermanager类
1.registerDriver(Driver driver) ;注册驱动
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
翻阅源码发现,通过API的方式注册驱动,Driver会new两次,所有推荐这种写法:
Class.forName("com.mysql.jdbc.Driver"); //当前就理解成 可以让com.mysql.jdbc.Driver里面的静态代码块执行
- getConnection(String url, String user, String password) ;与数据库建立连接
Connection接口
1.目标
能够使用Connection接口
2.讲解
-
概述
接口的实现在数据库驱动中。所有与数据库交互都是基于连接对象的。
-
作用
createStatement() ;创建执行sql语句对象
prepareStatement(String sql) ;创建预编译执行sql语句的对象
Statement接口
概述*
接口的实现在数据库驱动中. 用来操作sql语句(增删改查),并返回相应结果对象
ResultSet executeQuery(String sql) 根据查询语句返回结果集。只能执行select语句。
int executeUpdate(String sql) 根据执行的DML(insert update delete)语句,返回受影响的行数。
ResultSet接口
讲解
-
封装结果集,查询结果表的对象;
提供一个游标,默认游标指向结果集第一行之前。
调用一次next(),游标向下移动一行。
提供一些get方法。
-
ResultSet接口常用API
- boolean next();将光标从当前位置向下移动一行
- int getInt(int colIndex)以int形式获取ResultSet结果集当前行指定列号值
- int getInt(String colLabel)以int形式获取ResultSet结果集当前行指定列名值
- float getFloat(int colIndex)以float形式获取ResultSet结果集当前行指定列号值
- float getFloat(String colLabel)以float形式获取ResultSet结果集当前行指定列名值
- String getString(int colIndex)以String 形式获取ResultSet结果集当前行指定列号值
- String getString(String colLabel)以String形式获取ResultSet结果集当前行指定列名值
- Date getDate(int columnIndex); 以Date 形式获取ResultSet结果集当前行指定列号值
- Date getDate(String columnName);以Date形式获取ResultSet结果集当前行指定列名值
- void close()关闭ResultSet 对象
小结
3.1图解
封装案例
- 定义一个User类
package com.itheima.bean;
/**
* @Description: user实体类, 封装数据的
* @Author: yp
*/
public class User {
private int id;
private String username;
private String password;
private String nickname;
//提供get/set方法 Alt+Insert
public User() {
}
public User(int id, String username, String password, String nickname) {
this.id = id;
this.username = username;
this.password = password;
this.nickname = nickname;
}
public int getId() {
return id;
}
public void setId(int 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;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", nickname='" + nickname + '\'' +
'}';
}
}
- 封装
List<User> list = new ArrayList<User>();
while (resultSet.next()) {
//每遍历一次, 就是1条记录, 就封装成一个User对象
User user = new User(resultSet.getInt("id"),
resultSet.getString("username"),
resultSet.getString("password"),
resultSet.getString("nickname"));
list.add(user);
}
JDBC操作数据库练习
1.单元测试
1.1JUnit介绍
JUnit是一个Java语言的单元测试jar。属于第三方工具,一般情况下需要导入jar包,不过,多数Java开发环境已经集成了JUnit作为单元测试工具.编写测试类,简单理解可以用于取代java的main方法
1.2使用
-
在测试类方法上添加注解@Test
-
注解修饰的方法要求:public void 方法名() {…} ,方法名自定义,没有参数。
- 添加IDEA中集成的Junit库,使用快捷键“Alt+Enter”,点击“Add Junit …”
- 使用:选中方法右键,执行当前方法或者选中类名右键,执行类中所有方法(方法必须标记@Test)
1.3小结
-
常见使用错误,如果没有添加“@Test”,使用“Junit Test”进行运行,将抛异常
-
单元测试需要注意的地方:
2数据增删改查练习
package com.itheima.demo6_JDBC增删改查练习;
import com.itheima.demo1_JDBC快速入门.User;
import org.junit.Test;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
/**
* @Author:pengzhilin
* @Date: 2020/10/7 11:03
*/
public class Demo {
@Test
// 往day20_1数据库的user表中添加一条记录
public void insert() throws Exception{
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获得连接
String url = "jdbc:mysql://localhost:3306/day20_1";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// 3.创建执行sql语句对象
Statement statement = connection.createStatement();
// 4.执行sql语句,处理结果
String sql = "insert into user values(null,'zl','123456','赵六')";
int rows = statement.executeUpdate(sql);
System.out.println("受影响的行数:"+rows);
// 5.释放资源
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
}
@Test
// 修改day20_1数据库的user表中wangwu的密码为123456
public void update()throws Exception{
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获得连接
String url = "jdbc:mysql://localhost:3306/day20_1";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// 3.创建执行sql语句对象
Statement statement = connection.createStatement();
// 4.执行sql语句,处理结果
String sql = "update user set password = '123456' where username = 'wangwu'";
int rows = statement.executeUpdate(sql);
System.out.println("受影响的行数:"+rows);
// 5.释放资源
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
}
@Test
// 删除day20_1数据库的user表中id为3的记录
public void delete() throws Exception{
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获得连接
String url = "jdbc:mysql://localhost:3306/day20_1";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// 3.创建执行sql语句对象
Statement statement = connection.createStatement();
// 4.执行sql语句,处理结果
String sql = "delete from user where id = 3";
int rows = statement.executeUpdate(sql);
System.out.println("受影响的行数:"+rows);
// 5.释放资源
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
}
@Test
// 查询day20_1数据库的user表中所有记录
public void select1() throws Exception{
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获得连接
String url = "jdbc:mysql://localhost:3306/day20_1";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// 3.创建执行sql语句对象
Statement statement = connection.createStatement();
// 4.执行sql语句,处理结果
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);
// 创建ArrayList集合,限制集合元素类型为User
ArrayList<User> list = new ArrayList<>();
while (resultSet.next()) {
// 创建一个User对象,用来封装记录的列数据
User use = new User();
// 封装数据
use.setId(resultSet.getInt("id"));
use.setUsername(resultSet.getString("username"));
use.setPassword(resultSet.getString("password"));
use.setNickname(resultSet.getString("nickname"));
// 添加对象到集合
list.add(use);
}
// 5.释放资源
if (resultSet != null){
resultSet.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
//...
for (User user1 : list) {
System.out.println(user1);
}
}
@Test
// 查询day20_1数据库的user表中姓名为zs并且密码为123456的记录
public void select2() throws Exception{
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获得连接
String url = "jdbc:mysql://localhost:3306/day20_1";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// 3.创建执行sql语句对象
Statement statement = connection.createStatement();
// 4.执行sql语句,处理结果
String sql = "select * from user where username = 'zs' and password = '123456'";
ResultSet resultSet = statement.executeQuery(sql);
User use = null;
while (resultSet.next()) {
// 创建一个User对象,用来封装记录的列数据
use = new User();
// 封装数据
use.setId(resultSet.getInt("id"));
use.setUsername(resultSet.getString("username"));
use.setPassword(resultSet.getString("password"));
use.setNickname(resultSet.getString("nickname"));
}
// 5.释放资源
if (resultSet != null){
resultSet.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
// ...
if (use == null){
System.out.println("失败");
}else{
System.out.println("成功");
}
}
}
3.JDBC工具类的抽取
3.1步骤
- 在src目录下,创建配置文件
- 定义JdbcUtils类
- 定义getConnection()方法
- 定义release()方法
3.2实现
-
创建配置文件,配置文件在src目录下,扩展名是properties
配置文件:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day20_1
username=root
password=root
- 工具类实现
package com.itheima.Utils;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @Author:pengzhilin
* @Date: 2020/10/7 11:39
*/
public class JDBCUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
static {
try {
// 使用Properties对象加载配置文件
// 创建Properties对象
Properties pro = new Properties();
// 读取配置文件中的数据
//pro.load(new FileInputStream("day20\\src\\db.properties"));
// 返回的输入流的路径默认到src路径
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
pro.load(is);
// 通过pro对象给属性赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
// 1.加载驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获得连接
*
* @return 连接
* @throws Exception
*/
public static Connection getConnection() throws Exception {
// 2.获得连接
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 释放资源
*
* @param resultSet
* @param statement
* @param connection
*/
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.3小结
-
注意事项
-
配置文件建议定义在
src目录
-
使用的getProperty()方法里面的参数应该和配置文件里面的key一致, 加""
-
PreparedStatement
案例-登录案例
1.需求
在控制台输入用户名和密码,查询数据库,如果数据库存在当前用户,显示登录成功!
如果数据库不存在当前用户,显示登录失败!
2分析
2.1登录是做什么
- 登录说白了就是根据用户名和密码查询数据库, 如果能查询出来就是登录成功, 查询不出来就是登录失败
2.2思路分析
3.代码实现
- LoginClient.java
package com.itheima.demo5_登录案例;
import com.itheima.demo2_JDBC_API详解.User;
import com.itheima.demo4_JDBC工具类的抽取.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/**
* @Author:pengzhilin
* @Date: 2020/8/3 12:09
*/
public class Test {
public static void main(String[] args) throws Exception{
/*
需求:
在控制台输入用户名和密码,查询数据库,如果数据库存在当前用户,显示登录成功!
如果数据库不存在当前用户,显示登录失败!
*/
// 0.获取控制台输入的用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
// 1.注册驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建执行sql语句对象
Statement statement = connection.createStatement();
// 3.执行sql语句,处理结果 select * from user where username = 'zs' and password = '123456';
String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
ResultSet resultSet = statement.executeQuery(sql);
// 创建User对象,封装数据
User user = null;
while (resultSet.next()) {
user = new User();// 查询到了就new对象
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setNickname(resultSet.getString("nickname"));
}
// 4.释放资源
JDBCUtils.release(resultSet,statement,connection);
// 5.判断结果,显示信息(判断user对象是否为null,如果为null说明就没有查询到数据,否则就查询到了)
if (user == null){
// 没有查询到
System.out.println("登录失败!");
}else{
// 查询到
System.out.println("登录成功,欢迎回来!");
}
}
}
4.小结
4.1SQL注入问题出现
当输入的密码 ' or '' = '
, 发现永远登录成功
4.3SQL注入问题分析
- 输入的密码 ’ or ‘’ = ', 语句如下
SELECT * FROM user WHERE username ='zs' AND password = '' or '' = ''
SELECT * FROM user WHERE username ='zs' AND password = '' or true
SELECT * FROM user WHERE true
SELECT * FROM user
-
发现语句出现了sql注入问题
把用户输入的 or 当成关键词注入到了sql语句里面了
案例-登录中SQL注入问题解决
1.目标
能够完成PreparedStatement改造登录案例, 解决SQL注入问题
2.路径
- preparedStatement介绍
- preparedStatement的用法
3.讲解
3.1preparedStatement概述
预编译SQL语句对象, 是Statemen接口的子接口。
特点:
- 性能要比Statement高
- 会把sql语句先编译,格式固定好,
- sql语句中的参数会发生变化,过滤掉用户输入的关键字(eg: or)
3.2用法
3.2.1通过connection对象创建
-
connection.prepareStatement(String sql) ;创建prepareStatement对象
-
sql表示预编译的sql语句,如果sql语句有参数通过?来占位
SELECT * FROM user WHERE username = ? AND password = ?
3.2.2设置参数
- prepareStatement.set类型(int i,Object obj);参数1 i 指的就是问号的索引(指第几个问号,从1开始),参数2就是值 eg: setString(1,“zs”); setString(2,“123456”);
3.2.3 执行
-
ResultSet executeQuery(); 执行查询语句 int executeUpdate();执行增删改语句
4.实现
package com.itheima.demo6_登录中SQL注入问题解决;
import com.itheima.demo2_JDBC_API详解.User;
import com.itheima.demo4_JDBC工具类的抽取.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/**
* @Author:pengzhilin
* @Date: 2020/8/3 14:48
*/
public class Test {
public static void main(String[] args) throws Exception{
/*
需求:
在控制台输入用户名和密码,查询数据库,如果数据库存在当前用户,显示登录成功!
如果数据库不存在当前用户,显示登录失败!
*/
// 0.获取控制台输入的用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
// 1.注册驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建预编译sql语句对象
String sql = "select * from user where username = ? and password = ?";
PreparedStatement ps = connection.prepareStatement(sql);// 固定sql语句的格式
// 3.设置参数值
ps.setString(1,username);
ps.setString(2,password);
// 3.执行sql语句,处理结果
ResultSet resultSet = ps.executeQuery();// 注意: 这里不需要传sql语句
// 创建User对象,封装数据
User user = null;
while (resultSet.next()) {
user = new User();// 查询到了就new对象
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setNickname(resultSet.getString("nickname"));
}
// 4.释放资源
JDBCUtils.release(resultSet,ps,connection);
// 5.判断结果,显示信息(判断user对象是否为null,如果为null说明就没有查询到数据,否则就查询到了)
if (user == null){
// 没有查询到
System.out.println("登录失败!");
}else{
// 查询到
System.out.println("登录成功,欢迎回来!");
}
}
}
5.小结
- 注册驱动
- 获得连接
- 创建预编译SQL语句对象
- 设置参数
- 执行sql语句
- 释放资源
案例-使用preparedStatement完成CRUD
1.需求
通过PreparedStatement完成增、删、改、查
2.分析
- 注册驱动
- 获得连接
- 创建预编译sql语句对象
- 设置参数 执行
- 释放资源
3.实现
package com.itheima.demo10_使用preparedStatement完成CRUD;
import com.itheima.Utils.JDBCUtils;
import com.itheima.demo1_JDBC快速入门.User;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
/**
* @Author:pengzhilin
* @Date: 2020/10/7 15:11
*/
public class Demo {
@Test
// 往day20_1数据库的user表中插入一条记录
public void insert() throws Exception{
// 1.加载驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建预编译sql语句对象
String sql = "insert into user values(null,?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
// 3.设置参数
ps.setString(1,"tq" );
ps.setString(2,"123456" );
ps.setString(3,"田七" );
// 4.执行sql语句
int rows = ps.executeUpdate();
System.out.println("受影响的行数:"+rows);
// 5.释放资源
JDBCUtils.release(null, ps, connection);
}
@Test
// 修改day20_1数据库的user表中的id为7的密码为abcdef
public void update() throws Exception{
// 1.加载驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建预编译sql语句对象
String sql = "update user set password = ? where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
// 3.设置参数
ps.setString(1,"abcdef" );
ps.setInt(2,7 );
// 4.执行sql语句
int rows = ps.executeUpdate();
System.out.println("受影响的行数:"+rows);
// 5.释放资源
JDBCUtils.release(null, ps, connection);
}
@Test
// 删除day20_1数据库的user表中的id为5
public void delete() throws Exception{
// 1.加载驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建预编译sql语句对象
String sql = "delete from user where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
// 3.设置参数
ps.setInt(1,5 );
// 4.执行sql语句
int rows = ps.executeUpdate();
System.out.println("受影响的行数:"+rows);
// 5.释放资源
JDBCUtils.release(null, ps, connection);
}
@Test
// 查询day20_1数据库的user表中的所有数据
public void select1() throws Exception{
// 1.注册驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建预编译sql语句对象
String sql = "select * from user";
PreparedStatement ps = connection.prepareStatement(sql);
// 3.设置参数(不需要,因为sql语句没有参数)
// 4.执行sql语句,处理结果
ResultSet resultSet = ps.executeQuery();
// 创建ArrayList集合,限制集合元素类型为User
ArrayList<User> list = new ArrayList<User>();
while (resultSet.next()) {
// 创建User对象
User user = new User();
// 给属性赋值
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setNickname(resultSet.getString("nickname"));
// 添加到集合中
list.add(user);
}
// 5.释放资源
JDBCUtils.release(resultSet, ps, connection);
for (User user : list) {
System.out.println(user);
}
}
@Test
// 查询day20_1数据库的user表中姓名为zs
public void select2() throws Exception{
// 1.注册驱动,获得连接
Connection connection = JDBCUtils.getConnection();
// 2.创建预编译sql语句对象
String sql = "select * from user where username = ?";
PreparedStatement ps = connection.prepareStatement(sql);
// 3.设置参数
ps.setString(1,"zs" );
// 4.执行sql语句,处理结果
ResultSet resultSet = ps.executeQuery();
User user = null;
while (resultSet.next()) {
// 创建User对象
user = new User();
// 给属性赋值
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setNickname(resultSet.getString("nickname"));
}
// 5.释放资源
JDBCUtils.release(resultSet, ps, connection);
System.out.println(user);
}
}
4.小结
4.1步骤
- 注册驱动
- 获得连接
- 创建预编译sql语句对象
- 设置参数,
- 执行sql语句
- 释放资源
4.2API
- 创建预编译sql语句对象
connection.prepareStatement(String sql); //sql里面有参数, 先用?代替,进行占位
- 设置参数
prepareStatement.set类型(int 第几个问号,Object 值);
- 执行
Result result = prepareStatement.excuteQuery(); //执行查询 不传sql语句
int rows = prepareStatement.excuteUpdate(); //执行增删改 不传sql语句
4.3注意事项
-
?只能占参数,说白了就是列的值
-
?从1开始计数
-
执行的时候不要传入sql语句
事务
代码演示:
Connection中与事务有关的方法 | 说明 |
---|---|
setAutoCommit(boolean autoCommit) | 参数是true或false 如果设置为false,表示关闭自动提交,相当于开启事务; 类似sql里面的 start transaction; |
void commit() | 提交事务; 类似sql里面的 commit; |
void rollback() | 回滚事务; 类似sql里面的 rollback; |
public class Test {
public static void main(String[] args) throws Exception{
/*
Connection接口:
setAutoCommit(boolean autoCommit) 参数是true或false 如果设置为false,表示关闭自动提交,相当于开启事务; 类似sql里面的 start transaction;
void commit() 提交事务; 类似sql里面的 commit;
void rollback() 回滚事务; 类似sql里面的 rollback;
*/
// 1.注册驱动获得连接
Connection connection = JDBCUtils.getConnection();
// 2.开启事务
connection.setAutoCommit(false);
// 3.执行sql语句
String sql = "update user set password = ? where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
// 设置参数
ps.setString(1, "666666");
ps.setInt(2,6);
// 执行sql语句
int rows = ps.executeUpdate();
System.out.println("受影响的行数:"+rows);
// 4.提交或者回滚事务
//connection.commit();
connection.rollback();
// 5.释放资源
JDBCUtils.release(null,ps,connection);
}
}
public class Test {
public static void main(String[] args) {
/*
Connection接口:
setAutoCommit(boolean autoCommit) 参数是true或false 如果设置为false,表示关闭自动提交,相当于开启事务; 类似sql里面的 start transaction;
void commit() 提交事务; 类似sql里面的 commit;
void rollback() 回滚事务; 类似sql里面的 rollback;
*/
Connection connection = null;
PreparedStatement ps = null;
try {
// 1.注册驱动获得连接
connection = JDBCUtils.getConnection();
// 2.开启事务
connection.setAutoCommit(false);
// 3.执行sql语句
String sql = "update user set password = ? where id = ?";
ps = connection.prepareStatement(sql);
// 设置参数
ps.setString(1, "666666");
ps.setInt(2, 6);
// 执行sql语句
int rows = ps.executeUpdate();
System.out.println("受影响的行数:" + rows);
System.out.println(1/0);// 出现异常
// 4.提交或者回滚事务
connection.commit();
} catch (Exception e) {
e.printStackTrace();
// 如果有异常,就回滚事务
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
// 5.释放资源
JDBCUtils.release(null, ps, connection);
}
}
}
案例-转账案例
- 案例的准备工作
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values (null,'zs',1000);
insert into account values (null,'ls',1000);
insert into account values (null,'ww',1000);
1.需求
zs给ls转100, 使用事务进行控制
2.分析
3.实现
- 代码实现
public class Test {
public static void main(String[] args) {
// 需求: zs给ls转100, 使用事务进行控制
Connection connection = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
try {
// 1.注册驱动,获得连接
connection = JDBCUtils.getConnection();
// 2.***************开启事务***********************
connection.setAutoCommit(false);
// 3.书写sql语句,预编译sql语句,得到预编译对象
String sql1 = "update account set money = money - ? where name = ?";
String sql2 = "update account set money = money + ? where name = ?";
ps1 = connection.prepareStatement(sql1);
ps2 = connection.prepareStatement(sql2);
// 4.设置参数,执行sql语句
// zs账户-100
ps1.setDouble(1,100);
ps1.setString(2,"zs");
ps1.executeUpdate();
// 模拟张三-100之后,李四还没+100就出现异常
System.out.println(1/0);
// ls账户+100
ps2.setDouble(1,100);
ps2.setString(2,"ls");
ps2.executeUpdate();
// 5.***********************如果成功,就提交事务;******************
connection.commit();
} catch (Exception e) {
e.printStackTrace();
// 5.*********************如果失败,就回滚;**********************
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
// 6.释放资源
JDBCUtils.release(null,ps1,connection);
JDBCUtils.release(null,ps2,null);
}
}
}
4.小结
- 涉及到两个写的操作,我们一般通过手动事务去控制
- JDBC操作事务API
connection.setAutoCommit(fasle); //开启事务
connection.commit(); //提交事务
connection.rollback(); //回滚事务