标准的JDBC操作

释放双眼,带上耳机,听听看~!

User类,用来封装数据库中每一条user对象

package p01_jdbc;

import java.util.Date;

public class User {

    private int id ;
    private String username;
    private String password;
    private String email;
    private Date birthday;

    public int getId() {
        return id;
    }
    public void setId(int 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;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }  
}

JDBC_Utils类,用来提供与数据库连接关闭的方法

package p01_jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBC_Utils {
    //获取连接对象的方法
    public static Connection getCon(){
        Connection con = null ;
        try {
            //注册对象
            Class.forName("com.mysql.jdbc.Driver");
            //连接
            String jdbc_url = "jdbc:mysql://localhost:3306/jdbc";
            String jdbc_user = "root";
            String jdbc_password = "root";
            con = DriverManager.getConnection(jdbc_url,jdbc_user,jdbc_password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
    //关闭连接,释放资源
    public static void realse(ResultSet rs,Statement sta,Connection con) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(sta!=null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            sta = null ;
        }
        if(con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            con = null;
        }
    }
}

UserDao类,用来提供数据库的操作方法,增删改查等

其中PreparedStatement是预编译对象,使用PreparedStatement可以在sql语句中使用占位符,然后在执行之前为占位符赋值。

package p01_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 完成对数据库的增删改查
 * 
 * 
 * */
public class UserDao {
    //提供增加的方法
    public Boolean insert (User user) {
        Connection con = null ;
        Statement sta = null ;
        try {
            //获取连接
            con = JDBC_Utils.getCon();
            sta = con.createStatement();
            //执行sql
            Date birthday = user.getBirthday();
            String sqlBirthday = String.format("%tF", birthday);
            String sql = "insert into users(id,name,password,email,birthday)values("
                    + "'" + user.getId() + "',"
                    + "'" + user.getUsername() + "',"
                    + "'" + user.getPassword() + "',"
                    + "'" + user.getEmail() + "',"
                    + "'" + sqlBirthday + "')";
            int row = sta.executeUpdate(sql);
            if(row > 0 ) {
                return true;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            JDBC_Utils.realse(null, sta, con);
        }

        return false;
    }

    public List<User> findAllUser(){
        Connection con = null ;
        Statement sta = null ;
        ResultSet rs = null;
        try {
            //获取连接的对象
            con = JDBC_Utils.getCon();
            //获取执行sql语句的对象
            sta = con.createStatement();
            //执行sql语句
            String sql  = "select * from users";
            //遍历结果集
            List<User> list = new ArrayList<User>();
            rs = sta.executeQuery(sql);
            while(rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                java.sql.Date birthday = rs.getDate("birthday");
                user.setBirthday(birthday);
                list.add(user);
            }
            return list ;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            JDBC_Utils.realse(rs, sta, con);
        }
    }

    public User FindId(int id) {
        Connection con = null ;
        PreparedStatement sta = null ;
        ResultSet rs = null;
        try {
            //获取连接的对象
            con = JDBC_Utils.getCon();
            //sql语句
            String sql  = "select * from users where id = ?";
            /**
             * 获取执行sql语句的预编译对象
             * 预编译对象已经在这里传参编译过一次了,所以后面执行的时候不需要在进行传参
             * */
            sta = con.prepareStatement(sql);
            /**
             * 设定占位符的值
             * 第一个参数是设置第几个占位符
             * */
            sta.setInt(1, id);
            //执行查询
            rs = sta.executeQuery();
            //遍历结果
            if(rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                java.sql.Date birthday = rs.getDate("birthday");
                user.setBirthday(birthday);
                return user;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            JDBC_Utils.realse(rs, sta, con);
        }
        return null;
    }
}

对于主方法只需要关注传入参数的值即可。

package p01_jdbc;

import java.util.List;

public class FindAllUserTest {

    public static void main(String[] args) {
        /*
         * UserDao dao = new UserDao(); List<User> list = dao.findAllUser();
         * System.out.println(list.size());
         */
        UserDao dao = new UserDao();
        User user = dao.FindId(6);
        System.out.println(user.getUsername());
    }
}

人已赞赏
知识分享

数据库连接池DBCP与C3P0

2020-9-14 21:48:00

知识分享

PreparedStatement对象

2020-9-14 21:52:00

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索