一.编写DAO
1.封装连接的类
2.用户类
3.DAO抽象方法接口
4.实现DAO接口的类
5.测试类
在resources下的db.properties文件
#properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql
jdbc.username=root
jdbc.password=
init=2
maxactive=2
用户类:
DAO接口:
DAO实现类:
测试类:
1.封装连接的类
2.用户类
3.DAO抽象方法接口
4.实现DAO接口的类
5.测试类
在resources下的db.properties文件
#properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql
jdbc.username=root
jdbc.password=
init=2
maxactive=2
封装连接的类:
package day03;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class DBUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
private static int init;
private static int maxactive;
private static BasicDataSource bds=null;
static {
bds=new BasicDataSource();
Properties cfg=new Properties();
InputStream inStream=DBUtils.class
.getClassLoader()
.getResourceAsStream("db.properties");
try {
cfg.load(inStream);
driver=cfg.getProperty("jdbc.driver");
url=cfg.getProperty("jdbc.url");
username=cfg.getProperty("jdbc.username");
password=cfg.getProperty("jdbc.password");
init=Integer.parseInt(cfg.getProperty("init"));
maxactive=Integer.parseInt(cfg.getProperty("maxactive"));
//设置BasicDataSource必要参数
bds.setDriverClassName(driver);
bds.setUrl(url);
bds.setUsername(username);
bds.setPassword(password);
//设置BasicDataSource管理策略参数
bds.setInitialSize(init);
bds.setMaxActive(maxactive);
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn=null;
try {
conn=bds.getConnection();
return conn;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void closeConnection(Connection conn) {
if (conn!=null) {
try {
//把事务恢复成自动提交再归还
conn.setAutoCommit(true);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void rollback(Connection conn) {
if(conn!=null) {
try {
conn.rollback();
}catch(SQLException e1) {
e1.printStackTrace();
}
}
}
}
用户类:
package day03;
public class User {
//javaBean
private int id;
private String name;
private String pwd;
private int age;
public User() {
}
public User(int id, String name, String pwd, int age) {
super();
this.id = id;
this.name = name;
this.pwd = pwd;
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 String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
DAO接口:
package day03;
import java.util.List;
public interface UserDAO {
//通过ID查找用户
public User findUserById(int id);
//查询USER中所有的内容,并返回
public List<User> findAllUser();
//更改user表中某一条记录
public int updateUser(User user);
//
public int saveUser(User user);
}
DAO实现类:
package day03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class UserDAO1 implements UserDAO{
private static final String search_by_id
="select * from user_1 where id=?";
private static final String search_all
="select * from user_1";
private static final String update_user_password
="update user_1 set name=?,password=? where id=?";
private static final String insert_user
="insert into user_1 values(null,?,?,?)";
public User findUserById(int id) {
Connection conn=null;
try {
conn=DBUtils.getConnection();
conn.setAutoCommit(false);
PreparedStatement ps=conn.prepareStatement(search_by_id);
ps.setInt(1, id);
ResultSet rs=ps.executeQuery();
int i=0;
String name=null;
String pwd=null;
int age=0;
while(rs.next()){
i=rs.getInt(1);
name=rs.getString(2);
pwd=rs.getString(3);
age=rs.getInt(4);
return new User(i, name, pwd, age);
}
rs.close();
ps.close();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
DBUtils.rollback(conn);
}finally{
DBUtils.closeConnection(conn);
}
return null;
}
public List<User> findAllUser() {
Connection conn=null;
try {
conn=DBUtils.getConnection();
conn.setAutoCommit(false);
Statement sta=conn.createStatement();
ResultSet rs=sta.executeQuery(search_all);
List<User> list=new ArrayList<User>();
while (rs.next()) {
int i=rs.getInt(1);
String name=rs.getString(2);
String pwd=rs.getString(3);
int age=rs.getInt(4);
User user=new User(i, name, pwd, age);
list.add(user);
}
conn.commit();
return list;
} catch (Exception e) {
e.printStackTrace();
DBUtils.rollback(conn);
}finally {
DBUtils.closeConnection(conn);
}
return null;
}
public int updateUser(User user) {
Connection conn=null;
try {
conn=DBUtils.getConnection();
PreparedStatement ps=conn.prepareStatement(update_user_password);
ps.setString(1, user.getName());
ps.setString(2, user.getPwd());
ps.setInt(3, user.getId());
int n=ps.executeUpdate();
if (n!=1) {
throw new Exception("修改失败");
}
ps.close();
} catch (Exception e) {
e.printStackTrace();
DBUtils.rollback(conn);
}finally {
DBUtils.closeConnection(conn);
}
return 0;
}
public int saveUser(User user) {
Connection conn=null;
try {
conn=DBUtils.getConnection();
PreparedStatement ps=conn.prepareStatement(insert_user);
ps.setString(1, user.getName());
ps.setString(2, user.getPwd());
ps.setInt(3, user.getAge());
int n=ps.executeUpdate();
if (n!=1) {
throw new Exception("插入失败");
}
ps.close();
} catch (Exception e) {
e.printStackTrace();
DBUtils.rollback(conn);
}finally {
DBUtils.closeConnection(conn);
}
return 0;
}
}
测试类:
package day03;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class Demo5 {
public static void main(String[] args) {
//数据--->数据库读取出来的
//jdbc--->conn--->ps--->rs--->user
//List<User> arr_user=UserDAO.getUserAll();
//用户填写,代码生成的---->保存到库中
//user-->DAO-->sql-->conn--->ps-->db
//用户填写,代码生成的
/*User u1 = new User(1,"lily","123",12);
User u2 = new User(2,"rose","223",13);
List<User> list = new ArrayList<User>();
list.add(u1);
list.add(u2);*/
//验证dao的findUserById方法
UserDAO dao=new UserDAO1();
// User user=dao.findUserById(1);
// System.out.println(user.getName());
//验证dao的findall
List<User> list=dao.findAllUser();
// for (int i = 0; i < list.size(); i++) {
// System.out.println(list.get(i).getId());
// }
//验证dao的修改方法
// User user=new User(2, "xiongda", "321", 18);
// dao.updateUser(user);
//验证dao的插入方法
// User user=new User(1232, "xiongda", "321", 18);
// dao.saveUser(user);
//登录验证功能
Scanner scan = new Scanner(System.in);
System.out.println("请输入你的用户名:");
String name = scan.nextLine();
System.out.println("请输入你的密码:");
String pwd = scan.nextLine();
scan.close();
for(int i=0;i<list.size();i++) {
if (name.equals(list.get(i).getName()) && pwd.equals(list.get(i).getPwd())) {
System.out.println("登录成功");
break;
}
System.out.println("登录失败");
}
}
}