纯jdbc代码开发

来源：互联网发布：淘宝客服不说话怎么办编辑：程序博客网时间：2024/06/02 20:15

一、jdbc概念介绍

jdbc,即java database connectivity,是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序.我理解为就是使用java语言访问数据库数据的一套java语言接口，方法等。

二、准备工作

1.首先需要java连接数据库的jar包：mysql-connector-java-5.1.44-bin.jar。

2.然后dos命令窗口连接数据库，创建一张user用户表（mysql命令符使用可以查看这篇 http://blog.csdn.net/qq_36551763/article/details/78455442），我创建的表如下：

三、撸代码

1.先写一个对应的user类，代码如下：

public class User {    private int id;    private String username;    private String password;    private String sex;    private String address;    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 getSex() {        return sex;    }    public void setSex(String sex) {        this.sex = sex;    }    public String getAddress() {        return address;    }    public void setAddress(String address) {        this.address = address;    }    @Override    public String toString() {        return "User{" +                "id=" + id +                ", username='" + username + '\'' +                ", password='" + password + '\'' +                ", sex='" + sex + '\'' +                ", address='" + address + '\'' +                '}';    }}

2.创建一个数据库操作的工具类，核心内容

public class DBHelper {    public static final String url = "jdbc:mysql://localhost:3306/testdb1";//数据库地址，本篇采用自己主机电脑创建的数据库    public static final String name = "com.mysql.jdbc.Driver";//固定写法    public static final String users = "root";//数据库使用者的用户名    public static final String password = "hu19910127";//你访问数据库的密码    public Connection conn = null;    public PreparedStatement pst = null;    public static Connection getConnection(){        Connection conn = null;        try {            Class.forName(name);            conn = DriverManager.getConnection(url, users, password);//注册驱动，关键步骤        } catch (Exception e) {            e.printStackTrace();        }        return conn;    }    /**     * 插入记录     * @param user     * @return     */    public static int insert(User user){        Connection conn = getConnection();        String sql = "insert into user values(?, ?, ?,?,?)";//?是占位符，后面psmt通过位置给其设置参数        PreparedStatement psmt = null;        try {            psmt = conn.prepareStatement(sql);            psmt.setInt(1, user.getId());            psmt.setString(2, user.getUsername());            psmt.setString(3, user.getPassword());            psmt.setString(4, user.getSex());            psmt.setString(5, user.getAddress());            int count = psmt.executeUpdate();            return count;        } catch (SQLException e) {            e.printStackTrace();        }finally {            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(conn != null){                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        return -1;    }    /**     *通过id删除某一条记录     * @param id     * @return     */    public static int delete(Integer id){        Connection con = getConnection();        String sql = "delete from user where id=?";        PreparedStatement psmt = null;        try {            psmt = con.prepareStatement(sql);            psmt.setInt(1, id);            int count = psmt.executeUpdate();            return count;        } catch (SQLException e) {            e.printStackTrace();        }finally{            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }            if(con != null){                try {                    con.close();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }        }        return -1;    }    /**     * 通过id,修改对应的记录的信息     * @param user     */    public static void update(User user){        Connection con = getConnection();        String sql = "update user set username = ?,password = ?,sex = ?,address = ? where id = ?";        PreparedStatement psmt = null;        try {            psmt = con.prepareStatement(sql);            psmt.setString(1, user.getUsername());            psmt.setString(2, user.getPassword());            psmt.setString(3, user.getSex());            psmt.setString(4, user.getAddress());            psmt.setInt(5, user.getId());            psmt.execute();        } catch (Exception e) {            e.printStackTrace();        }finally{            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(con != null){                try {                    con.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }    /**     * 通过id查找单个user     * @param id     * @return     */    public static User queryById(Integer id){        Connection con = getConnection();        String sql = "select * from user where id = ?";        PreparedStatement psmt = null;        ResultSet resultSet = null;        User user=null;        try {            psmt = con.prepareStatement(sql);            psmt.setInt(1, id);            resultSet = psmt.executeQuery();            while (resultSet.next()){                user = new User();                user.setId(resultSet.getInt("id"));                user.setUsername(resultSet.getString("username"));                user.setPassword(resultSet.getString("password"));                user.setSex(resultSet.getString("sex"));                user.setAddress(resultSet.getString("address"));            }            return user;        } catch (Exception e) {            e.printStackTrace();        }finally{            if (resultSet != null){                try {                    resultSet.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(con != null){                try {                    con.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        return null;    }    /**     * 通过username查找所有符合条件的记录     * @param username     * @return     */    public static User queryByUsername(String username){        Connection con = getConnection();        String sql = "select * from user where username = ?";        PreparedStatement psmt = null;        User user=null;        try {            psmt = con.prepareStatement(sql);            psmt.setString(1, username);            ResultSet resultSet = psmt.executeQuery();            while (resultSet.next()){                user = new User();                user.setId(resultSet.getInt("id"));                user.setUsername(resultSet.getString("username"));                user.setPassword(resultSet.getString("password"));                user.setSex(resultSet.getString("sex"));                user.setAddress(resultSet.getString("address"));            }            return user;        } catch (Exception e) {            e.printStackTrace();        }finally{            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(con != null){                try {                    con.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        return null;    }    /**     * 通过address查找所有符合条件的记录     * @param address     * @return     */    public static List<User> queryByAddress(String address){        List<User> list = new ArrayList<>();        Connection con = getConnection();        String sql = "select * from user where address = ?";        PreparedStatement psmt = null;        User user=null;        try {            psmt = con.prepareStatement(sql);            psmt.setString(1, address);            ResultSet resultSet = psmt.executeQuery();            while (resultSet.next()){                user = new User();                user.setId(resultSet.getInt("id"));                user.setUsername(resultSet.getString("username"));                user.setPassword(resultSet.getString("password"));                user.setSex(resultSet.getString("sex"));                user.setAddress(resultSet.getString("address"));                list.add(user);            }            return list;        } catch (Exception e) {            e.printStackTrace();        }finally{            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(con != null){                try {                    con.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        return null;    }    /**     * 查询所有的记录,以集合的形式返回     * @return     */    public static List<User> queryAllUser(){        List<User> userList = new ArrayList<>();        Connection con = getConnection();        String sql = "select * from user";        PreparedStatement psmt = null;        User user = null;        try {            psmt = con.prepareStatement(sql);            ResultSet resultSet = psmt.executeQuery();            while (resultSet.next()){                user = new User();                user.setId(resultSet.getInt("id"));                user.setUsername(resultSet.getString("username"));                user.setPassword(resultSet.getString("password"));                user.setSex(resultSet.getString("sex"));                user.setAddress(resultSet.getString("address"));                userList.add(user);            }            return userList;        } catch (Exception e) {            e.printStackTrace();        }finally{            if(psmt != null){                try {                    psmt.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            if(con != null){                try {                    con.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        return null;    }}

结语：最原声的jdbc开发，核心内容就是那一步注册驱动，后面使用preparedstatement执行sql语句对数据库进行实质性的增删改查操作。

阅读全文

0 0