概念先参考：https://www.cnblogs.com/wuziyue/p/4827295.html
不要问为什么，直接代码搞起
1. com.domain 包 User 实体类
package com.domain;
public class User {
private String u_name;
private int u_age;
public String getU_name() {
return u_name;
}
public void setU_name(String u_name) {
this.u_name = u_name;
}
public int getU_age() {
return u_age;
}
public void setU_age(int u_age) {
this.u_age = u_age;
}
public User() {
}
public User(String u_name, int u_age) {
super();
this.u_name = u_name;
this.u_age = u_age;
}
@Override
public String toString() {
return "User [u_name=" + u_name + ", u_age=" + u_age + "]";
}
}
2. com.dao 包下的 IUserDao 接口
package com.dao;
import java.util.List;
import com.domain.User;
public interface IUserDao {
void addUserInfo(User user);
void deleteUserInfo(User user);
void updateUserAgeInfo(User user,int age);
User selectUserInfoByName(String name);
List<User> selectAllUserInfo();
}
3. com.dao.impl 包 UserDaoImpl 类
package com.dao.impl;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.Util.JDBCUtil;
import com.dao.IUserDao;
import com.domain.User;
public class UserDaoImpl implements IUserDao{
ResultSet rs = null;
Statement st = null;
User user = null;
/*常见的异常错误：No operations allowed after connection closed.
因为 当 数据库的连接Connection是一个Static的，程序共享这一个Connection。
那么第一次对数据库操作没问题，当把Connection关闭后，第二次还想操作数据库时Connection肯定不存在了。
所以为了解决这个问题：我们最好把 创建Connection的实例写到每个操作的方法中。
*/
/* 1. 增加用户信息*/
@Test
public void addUserInfo(User user){
Connection conn = JDBCUtil.getInstance().getConnection();
try {
st = conn.createStatement();
int row = st.executeUpdate("insert into tb_user (id,`name`,age)values(null,‘"+user.getU_name()+"‘,"+user.getU_age()+")");
if(row > 0){
System.out.println("添加用户成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtil.getInstance().closeAll(rs,st,conn);
}
/*2. 删除用户信息*/
@Test
public void deleteUserInfo(User user) {
Connection conn = JDBCUtil.getInstance().getConnection();
try{
st = conn.createStatement();
int row = st.executeUpdate("delete from tb_user where name = ‘"+user.getU_name()+"‘");
if(row > 0){
System.out.println("删除用户成功");
}
}catch (Exception e) {
e.printStackTrace();
}
JDBCUtil.getInstance().closeAll(rs,st,conn);
}
/*3. 修改用户信息*/
@Test
public void updateUserAgeInfo(User user,int age) {
Connection conn = JDBCUtil.getInstance().getConnection();
try{
st = conn.createStatement();
int row = st.executeUpdate("update tb_user set age = "+age+" where name = ‘"+user.getU_name()+"‘");
if(row > 0){
System.out.println("修改用户信息成功");
}
}catch (Exception e) {
e.printStackTrace();
}
JDBCUtil.getInstance().closeAll(rs,st,conn);
}
/*4. 通过name查询一个用户信息*/
@Test
public User selectUserInfoByName(String name) {
Connection conn = JDBCUtil.getInstance().getConnection();
//这里如果不写，当方法3中调用次方法时，就会因为 Connection 关闭而报错。
try{
st = conn.createStatement();
rs = st.executeQuery("select * from tb_user where `name` = ‘"+name+"‘");
while(rs.next()){
user = new User();
user.setU_name(rs.getString("name"));
user.setU_age(rs.getInt("age"));
}
}catch (Exception e) {
e.printStackTrace();
}
JDBCUtil.getInstance().closeAll(rs,st,conn);
return user;
}
/*5. 查询所有用户信息*/
@Override
public List<User> selectAllUserInfo() {
Connection conn = JDBCUtil.getInstance().getConnection();
List<User> userlist = new ArrayList();
try{
st = conn.createStatement();
rs = st.executeQuery("select * from tb_user");
while(rs.next()){
User user = new User();
user.setU_name(rs.getString("name"));
user.setU_age(rs.getInt("age"));
userlist.add(user);
}
}catch (Exception e) {
e.printStackTrace();
}
JDBCUtil.getInstance().closeAll(rs,st,conn);
return userlist;
}
}
4. com.Util 包 JDBCUtil 类 封装共用的代码
package com.Util;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
/*// 1. 参数是硬编码在代码中
private static String driverName = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/mysql001";
private static String userName = "root";
private static String passWorld = "root";
private static JDBCUtil instance = null;
Connection conn;
static{
try {
Class.forName(driverName);
instance = new JDBCUtil();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获得链接对象
public Connection getConn(){
try {
return DriverManager.getConnection(url,userName,passWorld);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭链接资源
public void close(ResultSet rs, Statement st, Connection conn){
try {
if(rs!=null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(st!=null) st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
*/
//2.把参数放到资源文件中
static Properties prop = new Properties();
//懒汉模式获取 JDBCUtil类的一个实例
private JDBCUtil(){}
private static JDBCUtil instance = null;
public static JDBCUtil getInstance(){
if(instance == null){
instance = new JDBCUtil();
}
return instance;
}
static{
try {
prop.load(new FileInputStream("jdbc.properties"));
Class.forName(prop.getProperty("driverName"));
} catch (Exception e) {
e.printStackTrace();
}
}
//获得链接对象
public Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(prop.getProperty("url"),prop.getProperty("userName"),prop.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//关闭所有链接资源
public void closeAll(ResultSet rs, Statement st, Connection conn){
try {
if(rs!=null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(st!=null) st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
5. com.test 包 TestDao 测试类
package com.test;
import java.util.List;
import java.util.Scanner;
import org.junit.Test;
import com.dao.IUserDao;
import com.dao.impl.UserDaoImpl;
import com.domain.User;
public class TestDao {
IUserDao userdao = new UserDaoImpl();
@Test
public void addUserInfo(){
User user = new User("赵本本",75); //获取数据
userdao.addUserInfo(user); //将数据传到Dao层进行数据库操作
}
@Test
public void deleteUserInfo(){
User user = new User("赵本本",75);
userdao.deleteUserInfo(user);;
}
/*修改用户信息*/
@Test
public void updateUserAgeInfo(){
System.out.println("请输入要修改的用户姓名:");
String name = new Scanner(System.in).nextLine();
//将name传到dao层验证该用户是否存在
User user = userdao.selectUserInfoByName(name);
if(user != null){
System.out.println("请输入修改后的用户年龄:");
int age = new Scanner(System.in).nextInt();
//将user对象和age传到dao层进行修改数据操作
userdao.updateUserAgeInfo(user,age);;
}else{
System.out.println("不存在该用户,gameover!!");
}
}
@Test
public void selectUserInfoByName(){
System.out.println("请输入查询的学生姓名");
String name = "李四";
System.out.println(userdao.selectUserInfoByName(name));
}
@Test
public void selectAllUserInfo(){
List<User> userlist = userdao.selectAllUserInfo();
for (User user : userlist) {
System.out.println(user);
}
}
}
6. 数据库表：
CREATE TABLE `tb_user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
7. jdbc.properties 配置文件
driverName = com.mysql.jdbc.Driver url = jdbc:mysql://localhost:3306/mysql001 userName = root password = root
待补充。。。。。。
原文：https://www.cnblogs.com/gshao/p/10236703.html