文章目录
1.前提准备
use day04_db;
create table user (
id int primary key auto_increment,
username varchar(20) not null,
birthday date,
sex char(1) default '男',
address varchar(50)
);
insert into user values (null, '孙悟空','1980-10-24','男','花果山水帘洞');
insert into user values (null, '白骨精','1992-11-12','女','白虎岭白骨洞');
insert into user values (null, '猪八戒','1983-05-20','男','福临山云栈洞');
insert into user values (null, '蜘蛛精','1995-03-22','女','盤丝洞');
select * from user;
2抽取JDBC工具类
抽取工具类的好处:
提高开发效率
减少代码量
2.1创建jdbc.properties文件
url=jdbc:mysql://localhost/day04_db
username=root
password=root
driverClass=com.mysql.jdbc.Driver
2.2抽取工具类
思想:抽取必要的方法为静态代码块,调用即执行
在静态代码块中创建注册驱动的方法
创建连接的方法getConnection
创建释放资源的方法release
代码:
package cn.itcast.utils;
import javax.xml.transform.Result;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
//思路:方便对数据库的使用,省去了大量的创建连接数据库使用语句
//基本步骤
//1.对基本的语句抽取成静态代码块
//2.创建连接的方法
//创建销毁的方法
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
try {
Properties properties= new Properties();
properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username= properties.getProperty("username");
password= properties.getProperty("password");
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.使用工具类对数据的增删改查操作
3.1查询所有
@Test
public void test01() throws Exception {
//查询所有
Connection connection = JDBCUtils.getConnection();
String sql = "select * from user";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String id = resultSet.getString("id");
String username = resultSet.getString("username");
String birthday = resultSet.getString("birthday");
String sex = resultSet.getString("sex");
String address = resultSet.getString("address");
System.out.println(id + username + birthday + sex + address);
}
JDBCUtils.release(connection,preparedStatement,resultSet);
}
3.2根据id查询
@Test
public void test02() throws Exception {
//通过ID查询
Connection connection = JDBCUtils.getConnection();
String sql="select * from user where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,2);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String id = resultSet.getString("id");
String username = resultSet.getString("username");
String birthday = resultSet.getString("birthday");
String sex = resultSet.getString("sex");
String address = resultSet.getString("address");
System.out.println(id + username + birthday + sex + address);
}
JDBCUtils.release(connection,preparedStatement,resultSet);
}
3.3添加数据
@Test
public void test03() throws Exception {
//添加数据
Connection connection = JDBCUtils.getConnection();
String sql = "insert into user values(null,?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"quanweiya");
preparedStatement.setString(2,"2020-06-18");
preparedStatement.setString(3, "男");
preparedStatement.setString(4, "北京");
int i = preparedStatement.executeUpdate();
if (i == 1) {
System.out.println("数据添加成功");
} else {
System.out.println("数据添加失败");
}
JDBCUtils.release(connection,preparedStatement,null);
}
3.4修改数据
@Test
public void test04() throws Exception {
//修改数据
Connection connection = JDBCUtils.getConnection();
String sql = "update user set username=? ,address=? where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "大太阳");
preparedStatement.setString(2,"北京朝阳区");
preparedStatement.setInt(3, 10);
int i = preparedStatement.executeUpdate();
if (i == 1) {
System.out.println("数据修改成功");
} else {
System.out.println("数据修改失败");
}
JDBCUtils.release(connection,preparedStatement,null);
}
3.5删除数据
@Test
public void test05() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "delete from user where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 10);
int i = preparedStatement.executeUpdate();
if (i == 1) {
System.out.println("数据删除成功");
} else {
System.out.println("数据删除失败");
}
JDBCUtils.release(connection,preparedStatement,null);
}