Java学习之路-JDBC学习

Java – 三层操作数据实例

实体类部分代码.

public class User  {

    private Integer id;
    private String username;
    private String password;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

数据库连接类代码.

import java.sql.*;

public class JDBCUtils {

    //加载驱动，建立数据库连接
    public static Connection getConnection(){
        Connection conn = null;
        try {
            String URL = "jdbc:mysql://localhost:3306/login";
            String NAME = "root";
            String PWD = "12345678";
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL,NAME,PWD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭数据库，释放资源
    public static void release(PreparedStatement pstemt,Connection conn){
        if(pstemt != null){
            try {
                pstemt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
	//关闭数据库释放资源
    public static void release(PreparedStatement pstemt, Connection conn, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        release(pstemt,conn);
    }
}

数据库操作类代码.

import java.sql.*;
import java.util.ArrayList;

/**
 * 数据库操作类
 */
public class UsersDao {

    //添加新用户的操作
    public boolean insert(User user){
        Connection conn = null;
        PreparedStatement pstemt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();//获得数据库的连接
            String sql = "insert into t_user(username,password) values (?,?)";
            pstemt = conn.prepareStatement(sql);
            pstemt.setString(1,user.getUsername());
            pstemt.setString(2,user.getPassword());
            int num = pstemt.executeUpdate();
            if(num > 0){
                return true;
            }else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pstemt,conn,rs);
        }
        return false;
    }

    //查询所有用户的操作
    public ArrayList<User> findAll(){
        Connection conn = null;
        PreparedStatement pstemt = null;
        ResultSet rs = null;
        ArrayList<User> users = new ArrayList<User>();
        String sql = "select * from t_user";
        try {
            conn = JDBCUtils.getConnection();//创建数据库连接
            pstemt = conn.prepareStatement(sql);
            rs = pstemt.executeQuery();//执行查询
            //处理结果集合
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pstemt,conn,rs);
        }
        return null;
    }

    //根据id查询指定的user
    public ArrayList<User> find(int id){
        Connection conn = null;
        PreparedStatement pstemt = null;
        ResultSet rs = null;
        ArrayList<User> users = new ArrayList<User>();
        String sql = "select * from t_user where id = ?";
        try {
            conn = JDBCUtils.getConnection();//获得数据库的连接
            pstemt = conn.prepareStatement(sql);//准备执行的sql语句
            rs = pstemt.executeQuery();//执行数据库语句
            //处理结果集
            if(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setUsername(rs.getString("password"));
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭数据库连接
            JDBCUtils.release(pstemt,conn,rs);
        }
        return null;
    }

    //删除用户
    public boolean delete(int id){
        Connection conn = null;
        PreparedStatement pstemt = null;
        ResultSet rs = null;
        try {
            String sql = "delete from t_user where id = ?";
            conn = JDBCUtils.getConnection();//获得数据库的连接
            pstemt = conn.prepareStatement(sql);//准备要执行的sql语句
            pstemt.setInt(1,id);
            int num = pstemt.executeUpdate();//执行sql语句
            if(num > 0){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pstemt,conn,rs);
        }
        return false;
    }

    //修改用户信息
    public boolean update(User user){
        Connection conn = null;
        PreparedStatement pstemt = null;
        ResultSet rs = null;
        String sql = "update t_user set username = ?,password = ? where id = ?";
        try {
            conn = JDBCUtils.getConnection();//创建数据库连接
            pstemt = conn.prepareStatement(sql);//准备要执行的sql语句
            pstemt.setString(1, user.getUsername());
            pstemt.setString(2,user.getPassword());
            pstemt.setInt(3,user.getId());
            int num = pstemt.executeUpdate();
            if(num > 0){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pstemt,conn,rs);
        }
        return false;
    }
}

测试类代码.

import java.util.ArrayList;

/**
 * 数据库测试类
 */
public class Test {

    public static void main(String[] args) {

        //实例化userdao
        UsersDao ud = new UsersDao();
        boolean flag = false;
        //向数据库中插入一条记录
        /*User user = new User();
        user.setUsername("test");
        user.setPassword("123456");
        ud.insert(user);*/

        //向数据库删除一条记录
        /*flag = ud.delete(2);
        if(flag == true){
            System.out.println("删除成功！");
        }else {
            System.out.println("删除失败！");
        }*/

        //更改数据库中的一条记录
        /*User user = new User();
        user.setId(1);
        user.setUsername("微风");
        user.setPassword("666666");
        flag = ud.update(user);
        if(flag == true){
            System.out.println("更新成功！");
        }else {
            System.out.println("更新失败！");
        }*/

        //查找数据库中的所有数据
        ArrayList<User> users = ud.findAll();
        //循环输出集合中的数据
        for (int i = 0;i < users.size();i++){
            System.out.println("第"+(i+1)+"条数据的username值为：" + users.get(i).getUsername());
        }
    }
}