JDBC使用流程
1.项目下创建lib文件夹,导入对应jar包
2.加载驱动
3.创建链接
4.获取Statement对象
5.创建sql语句
6.执行sql语句
7.释放资源
一、简单创建表
package cn.itsource.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.junit.Test;
/**
* 使用JDBC创建表
*
* @author kqs
*
* @2022年4月12日 下午7:01:19
*/
public class JdbcTest01 {
@Test
public void testName() throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEncoding = utf8", "root", "123456");
//获取Statement对象
Statement st = conn.createStatement();
//创建sql语句
String sql = "CREATE TABLE user1(id bigint, name varchar(50), age int);";
//调用方法,执行sql
st.execute(sql);
//释放资源
st.close();
conn.close();
}
}
二、完成CRUD
package cn.itsource.jdbc;
import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
/**
* 使用JDBC完成CRUD
* create
* retrieve
* update
* delete
* @author kqs
*
* @2022年4月12日 下午7:15:00
*/
public class JdbcTest02 {
/*
* 增
*/
@Test
public void testName01() throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEncoding = utf8", "root", "123456");
//获取Statement对象
Statement st = conn.createStatement();
//创建sql语句
String sql = "INSERT INTO user(id, name, age) VALUES (2, '符号', 32)";
//执行sql语句
st.executeUpdate(sql);
//关闭资源
st.close();
conn.close();
}
/*
* 删
* 根据id来删除
*/
@Test
public void testName02() throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEnconding = utf8","root","123456");
//获取Statement对象
Statement st = conn.createStatement();
//创建sql语句
String sql = "DELETE FROM user WHERE id = 1";
//执行语句
st.executeUpdate(sql);
//关闭资源
st.close();
conn.close();
}
/*
* 改
*/
@Test
public void testName03() throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEnconding = utf8", "root", "123456");
//获取Statement对象
Statement st = conn.createStatement();
//创建sql语句
String sql = "UPDATE user SET name = '张三' WHERE id = 1";
//执行sql语句
st.executeUpdate(sql);
//关闭资源
st.close();
conn.close();
}
/*
* 查
*/
@Test
public void testName04() throws Exception {
/*
* 查询全部
*/
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEnconding = utf8", "root", "123456");
//获取Statement对象
Statement st = conn.createStatement();
//创建sql语句
String sql = "SELECT * FROM user";
//执行方法,返回一个结果集对象
ResultSet res = st.executeQuery(sql);
//循环判断是否还有下一个元素
while (res.next()) {
//获取元素
long id = res.getLong("id");
String name = res.getString("name");
int age = res.getInt("age");
System.out.println("学号是:" + id + ", 姓名是:" + name + ", 年龄是:" + age);
}
//关闭资源
res.close();
st.close();
conn.close();
}
@Test
public void testName05() throws Exception {
/*
* 基于id查询数据
*/
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEnconding = utf8", "root", "123456");
//获取Statement对象
Statement st = conn.createStatement();
//创建sql语句
String sql = "SELECT * FROM user WHERE id = 2";
//执行sql语句,返回结果集对象
ResultSet res = st.executeQuery(sql);
//判断对象中是否还有元素,有则输出
while (res.next()) {
long id = res.getLong("id");
String name = res.getString("name");
int age = res.getInt("age");
System.out.println("学号是:" + id + ", 姓名是:" + name + ", 年龄是:" + age);
}
//关闭资源
res.close();
st.close();
conn.close();
}
}
三、利用配置文件和工具类
1.在项目下创建resource文件夹,里面放入properties文件
#存放加载文件、url、用户名和密码
className = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbc?characterEncoding = utf8
userName = root
password = 123456
2.创建一个JdbcUtil包,存放工具类
package cn.itsource.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
* 工具类的创建
*
* @author kqs
*
* @2022年4月12日 下午9:41:51
*/
public class JdbcUtil {
/**
* 使用配置文件动态获取参数信息
*/
//创建Properties类对象
private static Properties p = new Properties();
static {
try {
//类加载器获取流
ClassLoader loader = JdbcUtil.class.getClassLoader();
InputStream is = loader.getResourceAsStream("jdbc.properties");
//加载配置文件
p.load(is);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
//加载驱动
Class.forName(p.getProperty("className"));
//创建连接
Connection conn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("userName"), p.getProperty("password"));
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 创建连接
*/
/*static {
//随类的调用加载一次驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接的静态方法
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?CharacterEncoding = utf8", "root", "123456");
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}*/
/**
* 关闭流的方法
*/
/**
*
* @param conn 连接对象
* @param st 语句对象
* @param res 结果集对象
*/
public static void close(Connection conn, Statement st, ResultSet res) {
try {
if (conn != null)
conn.close();
}catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (st != null)
st.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (res != null)
res.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
3.创建domain包,里面创建User类
package cn.itsource.domain;
/**
* 创建一个domain对象,实体类
*
* @author kqs
*
* @2022年4月12日 下午10:34:11
*/
public class User {
private Long id;
private String name;
private Integer age;
public User(Long id, String name, Integer age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public User() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
4.创建Dao包,其中创建接口和实现类
package cn.itsource.dao;
import java.util.List;
import cn.itsource.domain.User;
/**
* 创建接口,定义dao层使用的1一些方法
*
* @author kqs
*
* @2022年4月12日 下午10:40:43
*/
public interface IUserDao {
/**
* 增
*/
void create(User user);
/**
* 删
*/
void delete(Long id);
/**
* 改
*/
void upDate(User user);
/**
* 查
*/
User retrieve(Long id);//基于id
List<User> retrieve();//全部查询
}
package cn.itsource.dao.Impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.itsource.dao.IUserDao;
import cn.itsource.domain.User;
import cn.itsource.util.JdbcUtil;
/**
* 实现IUserDao接口
*
* @author kqs
*
* @2022年4月12日 下午10:48:39
*/
public class UserDaoImpl implements IUserDao{
/**
* 增
*/
@Override
public void create(User user) {
Connection conn = null;
Statement st = null;
try {
//创建连接
conn = JdbcUtil.getConnection();
//获取Statement对象
st = conn.createStatement();
//创建sql语句
String sql = "INSERT INTO user(id, name, age) VALUES (" +
user.getId() + ", '" + user.getName() + "', " + user.getAge() + ")";
//执行语句并返回结果集对象
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JdbcUtil.close(conn, st, null);
}
}
/**
* 删除
*/
@Override
public void delete(Long id) {
Connection conn = null;
Statement st = null;
try {
//创建连接
conn = JdbcUtil.getConnection();
//获取Statement对象
st = conn.createStatement();
//创建sql语句
String sql = "DELETE FROM user WHERE id = " + id;
//执行sql语句
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JdbcUtil.close(conn, st, null);
}
}
/**
* 改
*/
@Override
public void upDate(User user) {
Connection conn = null;
Statement st = null;
try {
conn = JdbcUtil.getConnection();
st = conn.createStatement();
String sql = "UPDATE user SET name='"+user.getName()+"',age="+user.getAge()+" WHERE id="+user.getId();
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, st, null);
}
}
/**
* 根据id查询
*/
@Override
public User retrieve(Long id) {
Connection conn = null;
Statement st = null;
ResultSet res = null;
try {
conn = JdbcUtil.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM user WHERE id = " + id;
res = st.executeQuery(sql);
while (res.next()) {
long id1 = res.getLong("id");
String name = res.getString("name");
int age = res.getInt("age");
User user = new User(id1, name, age);
return user;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.close(conn, st, res);
}
return null;
}
/**
* 全表查询
*/
@Override
public List<User> retrieve() {
Connection conn = null;
Statement st = null;
ResultSet res = null;
try {
conn = JdbcUtil.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM user";
res = st.executeQuery(sql);
List<User> list = new ArrayList<>();
while (res.next()) {
long id = res.getLong("id");
String name = res.getString("name");
int age = res.getInt("age");
User user = new User(id, name, age);
list.add(user);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.close(conn, st, res);
}
return null;
}
}
5.创建test包,里面创建测试类
package cn.itsource.test;
import static org.junit.Assert.*;
import java.util.List;
import org.junit.Test;
import cn.itsource.dao.IUserDao;
import cn.itsource.dao.Impl.UserDaoImpl;
import cn.itsource.domain.User;
/**
* Dao层实现类的测试类
*
* @author kqs
*
* @2022年4月12日 下午11:06:28
*/
public class DaoTest {
/*
* 增加的测试单元
*/
@Test
public void testName01(){
try {
//创建对象
UserDaoImpl udi = new UserDaoImpl();
User user = new User(3L, "王五", 33);
udi.create(user);
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 删除的测试单元
*/
@Test
public void testName02() throws Exception {
UserDaoImpl udi = new UserDaoImpl();
udi.delete(1L);
}
/*
* 修改测试单元
*/
@Test
public void testName03() throws Exception {
UserDaoImpl udi = new UserDaoImpl();
User user = new User(3L, "大大师", 44);
udi.upDate(user);
}
/*
* 查询全表
*/
@Test
public void testName04() throws Exception {
UserDaoImpl udi = new UserDaoImpl();
List<User> list = udi.retrieve();
list.forEach(System.out::println);
}
/*
* 根据id查询
*/
@Test
public void testName05() throws Exception {
IUserDao udi = new UserDaoImpl();
User user = udi.retrieve(2L);
System.out.println(user);
}
}