一、普通对数据库进行增删查改
1、新建jdbc.properties
druid.username=root
druid.password=123456
druid.url=jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
druid.driverClassName=com.mysql.cj.jdbc.Driver
druid.keepAlive=300
druid.maxWait=300
druid.initialSize=10
druid.maxActive=20
2、新建BaseDao.java
主要是用来创建数据源和关闭连接的方法
public class BaseDao {
public static DataSource DATASOURCE = null;
/**
* 创建数据源
*/
static {
Properties properties = new Properties();
try {
properties.load(PoolTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
} catch (IOException e) {
e.printStackTrace();
}
DATASOURCE = new DruidDataSource();
((DruidDataSource)DATASOURCE).configFromPropety(properties);
}
/**
* 关闭连接
* @param connection
* @param statement
* @param resultSet
*/
public void closeAll(Connection connection, Statement statement, ResultSet resultSet){
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3、新建User.java
对应数据库中的表
public class User {
private int id;
private String uesrname;
private String password;
public User(int id, String uesrname, String password) {
this.id = id;
this.uesrname = uesrname;
this.password = password;
}
public User() {}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUesrname() {
return uesrname;
}
public void setUesrname(String uesrname) {
this.uesrname = uesrname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", uesrname='" + uesrname + '\'' +
", password='" + password + '\'' +
'}';
}
}
4、新建UserDao.java
UserDao继承BaseDao,就能直接调用BaseDao的方法,对数据库进行操作,这里展现的是对数据库增删查改的操作
public class UserDao extends BaseDao{
public static void main(String[] args) {
User user = new User(3,"王","123");
UserDao userDao = new UserDao();
// 保存
// int i = userDao.saveUser(user);
// System.out.println(i);
// 查询
List<User> users = userDao.selectUser();
System.out.println(users);
}
// 保存用户
public int saveUser(User user){
Connection connection = null;
PreparedStatement statement = null;
try {
String sql = "insert into user values(?,?,?)";
connection = DATASOURCE.getConnection();
statement = connection.prepareStatement(sql);
statement.setInt(1,user.getId());
statement.setString(2,user.getUesrname());
statement.setString(3,user.getPassword());
int i = statement.executeUpdate();
return i;
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll(connection,statement,null);
}
return -1;
}
// 删除用户
public int deleteUser(int id){
Connection connection = null;
PreparedStatement statement = null;
try {
String sql = "delete from user where id = ?";
connection = DATASOURCE.getConnection();
statement = connection.prepareStatement(sql);
statement.setInt(1,id);
int i = statement.executeUpdate();
return i;
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll(connection,statement,null);
}
return -1;
}
// 更新用户
public int updataUser(User user){
Connection connection = null;
PreparedStatement statement = null;
try {
String sql = "updata user set username = ?,password = ? where id = ?";
connection = DATASOURCE.getConnection();
statement = connection.prepareStatement(sql);
statement.setString(1,user.getUesrname());
statement.setString(2,user.getPassword());
statement.setInt(3,user.getId());
int i = statement.executeUpdate();
return i;
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll(connection,statement,null);
}
return -1;
}
// 查询用户
public List<User> selectUser(){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<User> users = new ArrayList<>();
try {
String sql = "select id,username,password from user ";
connection = DATASOURCE.getConnection();
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while (resultSet.next()){
users.add(new User(resultSet.getInt("id"),
resultSet.getString("username"),
resultSet.getString("password")));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll(connection,statement,resultSet);
}
return users;
}
}
二、用BaseDao对数据库进行增删查改
1、新建个BetterBaseDao作为接口
有泛型,然后写上几个增删查改的方法
public interface BetteerBaseDao<T> {
int save(T t);
int delete(int id);
List<T> selectAll();
T selectOne(int id);
int update(T t);
}
2、新建BaseDaoImpl.java实现BetterBaseDao
public class BaseDaoImpl<T> implements BetteerBaseDao<T>{
@Override
public int save(T t) {
return 0;
}
@Override
public int delete(int id) {
return 0;
}
@Override
public List<T> selectAll() {
return null;
}
@Override
public T selectOne(int id) {
return null;
}
@Override
public int update(T t) {
return 0;
}
}
3、新建UserDao2.java
继承BaseDaoImpl并实现BetterBaseDao,可以直接调用BaseDaoImpl里的方法了
public class UserDao2 extends BaseDaoImpl<User> implements BetteerBaseDao<User>{
public static void main(String[] args) {
UserDao2 userDao2 = new UserDao2();
userDao2.save(new User(6,"a","b"));
}
}
可能这时你还不太明白,我们用teacher这个数据库中存在的表举例
4、举例
Teacher.java
public class Teacher {
private int id;
private String name;
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public Teacher(){}
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;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
TeacherDao.java
public class TeacherDao extends BaseDaoImpl<Teacher> implements BetteerBaseDao<Teacher>{
}
UserDao2.java
public class UserDao2 extends BaseDaoImpl<User> implements BetteerBaseDao<User>{
public static void main(String[] args) {
UserDao2 userDao2 = new UserDao2();
userDao2.save(new User(6,"a","b"));
TeacherDao teacherDao = new TeacherDao();
teacherDao.save(new Teacher(4,"123"));
}
}
这时候我们能运行成功了,但是由于BaseDaoImpl.java中没有写出那几个方法的代码,所以这还是空架子,这时候我们去填补BaseDaoImpl.java的代码。里面的代码不完全,老师只讲到这里,说实话我自己不会写。
BaseDaoImpl.java
public class BaseDaoImpl<T> implements BetteerBaseDao<T>{
public static DataSource DATASOURCE = null;
static {
Properties properties = new Properties();
try {
properties.load(PoolTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
} catch (IOException e) {
e.printStackTrace();
}
DATASOURCE = new DruidDataSource();
((DruidDataSource)DATASOURCE).configFromPropety(properties);
}
public void closeAll(Connection connection, Statement statement, ResultSet resultSet){
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public int save(T t) {
Object object = (Object)t;
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//假设传进来的是USER
// 1、定义sql
Class<?> aClass = t.getClass();
String tName = aClass.getName().substring(aClass.getName().lastIndexOf(".") + 1).toLowerCase();
Field[] fields = aClass.getDeclaredFields();
StringBuilder sql = new StringBuilder();
sql.append("insert into ").append(tName).append("values(");
for (int i = 0; i < fields.length; i++) {
sql.append("?,");
}
sql.append(")");
String finalSql = sql.toString().replace(",)", ")");
System.out.println(finalSql);
// 2、获取连接
connection = DATASOURCE.getConnection();
preparedStatement = connection.prepareStatement(finalSql);
// 3、preparedStatement设置值
for (int i = 1; i < fields.length; i++) {
fields[i-1].setAccessible(true);
if (fields[i-1].getType() == int.class){
preparedStatement.setInt(i,fields[i-1].getInt(object));
}else if (fields[i-1].getType() == String.class){
preparedStatement.setString(i,fields[i-1].get(object).toString());
}
}
// 4、执行
return preparedStatement.executeUpdate();
} catch (SQLException e1) {
e1.printStackTrace();
} catch (IllegalAccessException e1) {
e1.printStackTrace();
} finally {
closeAll(connection,preparedStatement,null);
}
return -1;
}
@Override
public int delete(int id) {
return 0;
}
@Override
public List<T> selectAll(Class clazz) {
Connection connection = null;
PreparedStatement preparedStatement = null;
//T表示传进来的class
List<T> list = new ArrayList<>();
try{
// 1、定义sql
String tName = clazz.getName().substring(clazz.getName().lastIndexOf(".") + 1).toLowerCase();
Field[] fields = clazz.getDeclaredFields();
StringBuilder sql = new StringBuilder();
sql.append("select * from ").append(tName);
// 2、获取连接
connection = DATASOURCE.getConnection();
preparedStatement = connection.prepareStatement(sql.toString());
// 4、执行
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
// 这里用到了反射
Object object = clazz.newInstance();
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
if (fields[i].getType() == int.class){
fields[i].setInt(object,resultSet.getInt(fields[i].getName()));
}else if (fields[i].getType() == String.class){
fields[i].set(object,resultSet.getString(fields[i].getName()));
}
}
list.add((T)object);
}
} catch (SQLException e1) {
e1.printStackTrace();
} catch (IllegalAccessException e1) {
e1.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
closeAll(connection,preparedStatement,null);
}
return list;
}
@Override
public T selectOne(int id) {
return null;
}
@Override
public int update(T t) {
return 0;
}
}
然后再测试
public class UserDao2 extends BaseDaoImpl<User> implements BetteerBaseDao<User>{
public static void main(String[] args) {
UserDao2 userDao2 = new UserDao2();
//userDao2.save(new User(4,"a","b"));
List<User> users = userDao2.selectAll(User.class);
System.out.println(users);
TeacherDao teacherDao = new TeacherDao();
//teacherDao.save(new Teacher(6,"123"));
List<Teacher> teachers = teacherDao.selectAll(Teacher.class);
System.out.println(teachers);
}
}
测试之后会发现数据库里的数据都出来了,以上就是自己写BaseDao