JDBC--面向对象实现

JDBC API工作的四个环节：
1.DriverManager类：依据数据库的不同，管理JDBC驱动。
2.Connection接口：负责连接数据库并担任传送数据的任务。
3.Statement接口：由Connection产生，负责执行SQL语句。
4.ResultSet接口：负责保存Statement执行后所产生的查询结果。

一、bin包

package bean;
/*
1.对应数据库中的表UserInfo
2.bean包中的类一般都以Info结尾，以表名开头，首字母大写
 */
public class UserInfo {
    /*
    1.属性和表中的列一一对应
    2.属性名首字母小写，驼峰
    3.属性和列类型对应
    4.属性名尽量和列名一致 user_id--->userId
     */
    private Integer userId;
    private String userName;
    private String userSex;
    private Integer userAge;
    private String userAddress;
    private String userPass;
    private Integer userState;

    /*封装类
    必写！ alt+insert
    1.get set方法
    2.构造器
    3.toString（）方法
     */
    public UserInfo() {
    }

    public Integer getUserId() {
        return userId;
    }

    public String getUserName() {
        return userName;
    }

    public String getUserSex() {
        return userSex;
    }

    public Integer getUserAge() {
        return userAge;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public String getUserPass() {
        return userPass;
    }

    public Integer getUserState() {
        return userState;
    }


    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public void setUserAge(Integer userAge) {
        this.userAge = userAge;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    public void setUserPass(String userPass) {
        this.userPass = userPass;
    }

    public void setUserState(Integer userState) {
        this.userState = userState;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userSex='" + userSex + '\'' +
                ", userAge=" + userAge +
                ", userAddress='" + userAddress + '\'' +
                ", userPass='" + userPass + '\'' +
                ", userState=" + userState +
                '}'+"\n";
    }
}

二、db包

package db;

import java.sql.*;

//负责连接和关闭数据库
public class DBManager {
    private DBManager(){

    }

    /**
     * 这个能够获取到数据库的连接
     * user:root
     * passwd:root
     * 数据库：db1
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false";
            String user = "root";
            String password = "root";
            conn = DriverManager.getConnection(url,user,password);
        }catch(ClassNotFoundException e){
            System.out.println("未找到类！");
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return conn;
    }

    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
       try{
           if (rs!=null){
               rs.close();
           }
           ps.close();
           conn.close();
       }catch(SQLException e){
           System.out.println(e.getMessage());
       }
    }
}

三、dao包

package dao;

import bean.UserInfo;
import db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
1.DAO:Data Access Object
2.一般以bean包的类名为前缀
3.dao包中类负责执行CRUD操作
4.一个dao类负责一个表的CRUD，也可以说成是对一个bean类的CRUD（增删改查）
 */
public class UserInfoDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;
    //根据主键获取对应行
    public UserInfo findUserById(int userId){
        UserInfo ui = new UserInfo();
        String sql = "select * from userinfo where userid = ?";
        conn = DBManager.getConnection();
       try {
           ps = conn.prepareStatement(sql);
           ps.setInt(1, userId);
           rs = ps.executeQuery();
           if (rs.next()) {
               ui.setUserId(rs.getInt(1));
               ui.setUserName(rs.getString(2));
               ui.setUserSex(rs.getString(3));
               ui.setUserAge(rs.getInt(4));
               ui.setUserAddress(rs.getString(5));
               ui.setUserPass(rs.getString(6));
               ui.setUserState(rs.getInt(7));
           }
       }catch(SQLException e){
           System.out.println(e.getMessage());
       }finally {
           DBManager.close(conn,ps,rs);
       }
       return ui;
    }

    //获取所有行数据
    public List<UserInfo> findAllUser(){
        List<UserInfo> list = new ArrayList<>();
        String sql = "select * from userinfo where userstate = 1";
        conn = DBManager.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                UserInfo ui = new UserInfo();//放到外面则只有一条重复的数据，因为只有一个对象
                ui.setUserId(rs.getInt(1));
                ui.setUserName(rs.getString(2));
                ui.setUserSex(rs.getString(3));
                ui.setUserAge(rs.getInt(4));
                ui.setUserAddress(rs.getString(5));
                ui.setUserPass(rs.getString(6));
                ui.setUserState(rs.getInt(7));
                list.add(ui);
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally {
            DBManager.close(conn,ps,rs);
        }
        return list;
    }

    //添加用户信息到数据库
    public boolean saveUser(UserInfo ui){
        boolean b = false;
        String sql = "insert into userinfo values(?,?,?,?,?,md5(?),1)";
        conn = DBManager.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,ui.getUserId());
            ps.setString(2,ui.getUserName());
            ps.setString(3,ui.getUserSex());
            ps.setInt(4,ui.getUserAge());
            ps.setString(5,ui.getUserAddress());
            ps.setString(6,ui.getUserPass());
            int result = ps.executeUpdate();
            System.out.println("共"+result+"行受到影响");
            if (result>0){
                b = true;
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally {
            DBManager.close(conn,ps,null);
        }
        return b;
    }

    //删除指定用户 非推荐写法
    public boolean removeUser(int userId){
        boolean b = false;
        String sql = "delete from userinfo where userid = ?";
        conn = DBManager.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,userId);
            int result = ps.executeUpdate();
            if (result>0){
                b = true;
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally {
            DBManager.close(conn,ps,null);
        }
        return b;
    }

    //删除指定用户信息，其实是修改了状态  推荐写法
    public boolean deleteUser(int userId){
        boolean b = false;
        String sql = "update userinfo set userstate = 0 where userid = ?";
        conn = DBManager.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,userId);
            int n = ps.executeUpdate();
            if (n>0){
                b = true;
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally {
            DBManager.close(conn,ps,null);
        }
        return b;
    }

    //修改指定用户信息，不带修改密码
    public boolean updateUser(UserInfo ui){
        boolean b = false;
        String sql = "update userinfo set username = ?,usersex = ?,userage = ?,userAddress = ? where userId = ?";
        conn = DBManager.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(5,ui.getUserId());
            ps.setString(1,ui.getUserName());
            ps.setString(2,ui.getUserSex());
            ps.setInt(3,ui.getUserAge());
            ps.setString(4,ui.getUserAddress());
            int result = ps.executeUpdate();
            if (result>0){
                b = true;
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally {
            DBManager.close(conn,ps,null);
        }
        return b;
    }

    //分页
    public List<UserInfo> findUserByPage(int page,int size){
        List<UserInfo> list = new ArrayList<>();
        String sql = "select * from userinfo where userstate = 1 limit ?,?";
        conn = DBManager.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*size);
            ps.setInt(2,size);
            rs = ps.executeQuery();
            while(rs.next()){
                UserInfo ui = new UserInfo();
                ui.setUserId(rs.getInt(1));
                ui.setUserName(rs.getString(2));
                ui.setUserSex(rs.getString(3));
                ui.setUserAge(rs.getInt(4));
                ui.setUserAddress(rs.getString(5));
                ui.setUserPass(rs.getString(6));
                ui.setUserState(rs.getInt(7));
                list.add(ui);
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }finally {
            DBManager.close(conn,ps,rs);
        }
        return list;
    }
}

四、test

package test;

import bean.UserInfo;
import dao.UserInfoDAO;

import javax.jws.soap.SOAPBinding;
import java.util.List;

public class Test {
    private static UserInfoDAO uidao = new UserInfoDAO();
    public static void a(){
        UserInfo ui = uidao.findUserById(1);
        System.out.println(ui);
    }

    public static void b(){
        List<UserInfo> list = uidao.findAllUser();
        System.out.println(list);
    }

    public static void c(){
         UserInfo us = new UserInfo();
         us.setUserId(88);
         us.setUserName("bbb");
         us.setUserSex("男");
         us.setUserAge(18);
         us.setUserAddress("北京");
         us.setUserPass("123");
         us.setUserState(1);
         boolean judge = uidao.saveUser(us);
         System.out.println(judge?"插入成功！":"插入失败！");
    }

    public static void d(){
        boolean judge = uidao.removeUser(88);
        System.out.println(judge?"删除成功！":"删除失败！");
    }

    public static void e(){
        boolean judge = uidao.deleteUser(87);
        System.out.println(judge?"删除成功！":"删除失败！");
    }

    public static void f(){
        UserInfo us = new UserInfo();
        us.setUserId(86);
        us.setUserName("ddd");
        us.setUserSex("男");
        us.setUserAge(18);
        us.setUserAddress("北京");
        us.setUserPass("123");
        us.setUserState(1);
        boolean judge = uidao.updateUser(us);
        System.out.println(judge?"更改成功！":"更改失败！");
    }

    public static void g(int page,int size){
        List<UserInfo> list = uidao.findUserByPage(page,size);
        System.out.println(list);
    }

    public static void main(String[] args) {
        g(1,10);
    }
}