下载jdbc连接mysql驱动包-----使用的是mysql数据库
可在官网 https://dev.mysql.com/ 下载 mysql-connector-java-5.1.37-bin.jar
一、建立jdbc的配置文件db.properties
#jdbc链接配置
#驱动
classDriver=com.mysql.cj.jdbc.Driver
#要连接数据库的地址
url=jdbc:mysql://localhost:3306/shop
#数据库用户名
username=root
#数据库密码
password=123456
二、建立要操作表的结构的对象----User.java
public class User {
private int id;
private String name;
private int age;
public User() {
}
public User(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
三、创建一个接口,定义里面是操作表的方法
import java.util.List;
public interface UserDAO {
//插入
boolean insert(User user);
//删除
boolean delete(int id);
boolean delete(String name);
//改
boolean update(User user);
//查
User selectOne(int id);
List<User> selectAll(String name);
List<User> selectAll();
}
四、创建接口的实现类
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDAOImpl implements UserDAO{
@Override
public boolean insert(User user) {
//获得数据库连接对象
Connection connection = JDBCUtil.getConnection();
//获得数据库sql语句执行平台
try {
Statement statement = connection.createStatement();
boolean execute = statement.execute("insert into user(name,age) values ('" + user.getName() + "','" + user.getAge() + "')");
statement.close();
connection.close();
return true;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
@Override
public boolean delete(int id) {
//获得数据库连接对象
Connection connection = JDBCUtil.getConnection();
try {
//获得sql语句执行平台
PreparedStatement preparedStatement = connection.prepareStatement("delete from user where id = ?");
preparedStatement.setString(1,id+"");
//语句执行
preparedStatement.execute();
preparedStatement.close();
connection.close();
return true;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
@Override
public boolean delete(String name) {
//获得数据库连接
Connection connection = JDBCUtil.getConnection();
try {
PreparedStatement preparedStatement = connection.prepareStatement("delete from user where name = ?");
//给sql语句的 ? 赋值
preparedStatement.setString(1,name);
//语句执行
preparedStatement.execute();
//关闭
preparedStatement.close();
connection.close();
return true;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
@Override
public boolean update(User user) {
//获得数据库的连接
Connection connection = JDBCUtil.getConnection();
//获得sql语句执行平台
try {
Statement statement = connection.createStatement();
//注意一定要 '' 里面再加""进行参数拼接,否则不成功
boolean execute = statement.execute("update user set name = '"+user.getName()+"',age = '"+user.getAge()+"'where id = '"+user.getId()+"' ");
statement.close();
connection.close();
return true;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
@Override
public User selectOne(int id) {
//获取数据库连接对象
Connection connection = JDBCUtil.getConnection();
//获得sql语句执行平台
try {
Statement statement = connection.createStatement();
//获得结果集
ResultSet resultSet = statement.executeQuery("select * from user where id = '" + id + "'");
//遍历结果集,创建相应对象
if(resultSet.next()){
int id1 = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
return new User(id,name,age);
}
statement.close();
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override
public List<User> selectAll(String name) {
//创建一个集合进行存储user对象
List<User> list = new ArrayList<>();
//获得数据库连接对象
Connection connection = JDBCUtil.getConnection();
try {
//获得sql语句执行平台
PreparedStatement preparedStatement = connection.prepareStatement("select * from user where name = ?");
//为sql语句的 ? 赋值
preparedStatement.setString(1,name);
//执行sql语句,获得结果集
ResultSet resultSet = preparedStatement.executeQuery();
//遍历结果集
while (resultSet.next()){
int id = resultSet.getInt("id");
String name1 = resultSet.getString("name");
int age = resultSet.getInt("age");
//添加查找后,生成的对象,将其存储在集合里面
list.add(new User(id,name,age));
}
preparedStatement.close();
connection.close();
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
@Override
public List<User> selectAll() {
//创建一个集合进行存储user对象
List<User> list = new ArrayList<>();
//获得数据库连接对象
Connection connection = JDBCUtil.getConnection();
//获得sql语句执行平台
try {
Statement statement = connection.createStatement();
//传入sql语句
ResultSet resultSet = statement.executeQuery("select * from user");
//遍历结果集
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
list.add(new User(id,name,age));
}
statement.close();
connection.close();
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
}
五、创建一个获得连接数据库的工具类
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil {
//获取连接数据库的相关变量
//数据库连接地址
private static String url;
//数据连接密码
private static String password;
//数据库连接账号名
private static String name;
//数据库驱动类
private static String driver;
//在加载类时,就会进行数据库驱动的加载和参数的准备
static {
//通过反射,进行数据库连接配置文件的获取
Properties properties = new Properties();
try {
properties.load(new FileReader("E:\\IdeaProjects\\JDBC_Test\\db.properties"));
//通过配置资源文件的参数获取,进行相关数据库连接参数的初级化
driver = properties.getProperty("classDriver");
name = properties.getProperty("username");
password = properties.getProperty("password");
url = properties.getProperty("url");
//通过反射,进行数据库驱动配置
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获得数据库连接对象Connection
public static Connection getConnection(){
Connection connection = null;
try {
connection = DriverManager.getConnection(url,name,password);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//数据库事务
// connection.setAutoCommit(false);
// connection.commit();
// connection.rollback();
return connection;
}
}
六、在实现类中,调用工具类的方法,获得数据库连接对象,进行相关增删改查
import java.sql.Connection;
import java.util.List;
public class Main {
public static void main(String[] args) {
//一、建立jdbc的配置文件db.properties
/*
* 配置文件有键值对
* driverClass:驱动程序类 ------> com.mysql.cj.jdbc.Driver
* url:数据库链接地址:jdbc:mysql://localhost:3306/数据库名称 ---> jdbc:mysql://localhost:3306/xiaozhao
* username:数据库用户名-----> root
* password:数据库链接密码-----> 123456
* */
//二、建立要操作表的结构的对象
/*
* 比如要操作表user,里面有id,name,age的字段
* 创建该user类
* */
//三、创建一个接口,定义里面是操作表的方法
//四、创建接口的实现类
//五、创建一个获得连接数据库的工具类
//六、在实现类中,调用工具类的方法,获得数据库连接对象,进行相关增删改查
Connection connection = JDBCUtil.getConnection();
System.out.println(connection);
UserDAOImpl userDAO = new UserDAOImpl();
// boolean flag = userDAO.insert(new User(0, "小胖", 22));
// System.out.println("添加"+flag);
// boolean delete = userDAO.delete(7);
// System.out.println("删除"+delete);
// boolean flag = userDAO.delete("小明");
// System.out.println("删除"+flag);
// boolean flag = userDAO.update(new User(8, "你好", 10));
// System.out.println(flag);
// User user = userDAO.selectOne(8);
// System.out.println(user);
// List<User> list = userDAO.selectAll("小明");
// System.out.println(list);
List<User> list = userDAO.selectAll();
System.out.println(list);
}
}
将数据库的增删改封装为一个方法,数据库的查找封装为一个方法
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//将数据库的增删改封装为一个方法
//数据库的查找封装为一个方法
public class JDBCTemple {
public static void excute(String sql,Object ...argus) throws Exception {
//获得驱动
Connection connection = JDBCUtil.getConnection();
//获得 预处理 sql 平台
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//为sql语句的 ? 赋值
for (int i = 0; i < argus.length; i++) {
//是从1开始的
preparedStatement.setObject(i+1,argus[i]);
}
//执行
preparedStatement.execute();
preparedStatement.close();
connection.close();
}
public static <T> List<T> excuteQuery(T t,String sql,Object ...args) throws Exception {
//创建一个集合进行存储user对象
List<T> list = new ArrayList<>();
//创建一个对象
Class<?> aClass = t.getClass();
//根据字节码对象,创建其实现对象
T o = (T) aClass.newInstance();
//获得数据库连接对象
Connection connection = JDBCUtil.getConnection();
//获得sql语句执行平台
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//为sql语句的 ? 赋值
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
//执行sql语句,获得结果集
ResultSet resultSet = preparedStatement.executeQuery();
//遍历结果集
while (resultSet.next()){
//通过反射,知道其表对应的对象类有多少个字段
BeanInfo beanInfo = Introspector.getBeanInfo(User.class, Object.class);
//返回 bean 的所有属性的描述符
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
//进行遍历属性描述,获得其相关属性及其操作方法
for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
//获得其属性名
String name = propertyDescriptor.getName();
//根据属性名,获得其表相关属性
Object name1 = resultSet.getObject("name");
//将这个值 放到java 对象
Object invoke = propertyDescriptor.getWriteMethod().invoke(o, name1);
}
//添加查找后,生成的对象,将其存储在集合里面
list.add(o);
}
preparedStatement.close();
connection.close();
return list;
}
}
### 代码结构截图
![在这里插入图片描述](https://img-blog.csdnimg.cn/d667644ba1fd4bbfa5002e3770e32a87.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5aSn6LC3b3Zv,size_16,color_FFFFFF,t_70,g_se,x_16)