JDBC的运用

使用了好久ORM框架，底层的JDBC操作就不会写了，现在回忆一下

1、注册驱动
2、建立连接
3、创建执行SQL的语句
4、执行语句
5、处理执行结果
6、释放资源

---

JdbcUtils.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public final class JdbcUtils {    private  static String url = "jdbc:mysql://localhost:3306/test";    private  static String root = "root";    private  static String password = "root";    //单例模式    private static JdbcUtils instance =null;    private JdbcUtils(){    }    public static JdbcUtils getInstance(){    //延时加载        if(instance == null){            //并发            synchronized(JdbcUtils.class){            if(instance == null){                instance =new JdbcUtils();            }        }    }        return instance; }    //静态代码块    static{        try {            Class.forName("com.mysql.jdbc.Driver");        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public static Connection getConnection() throws Exception{        return DriverManager.getConnection(url, root, password);    }    public static void free(ResultSet rs,PreparedStatement ps,Connection conn){        if (rs != null)            try {                rs.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        if (ps != null)            try {                ps.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        if (conn != null)            try {                conn.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }    }}

具体的操作数据库代码DbOperate .java

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.pojo.StudentUser;public class DbOperate {    //注册功能    public static boolean create(String number,String username,String password,String usergroud){        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            String sql="insert into user_info(number,username,password,usergroud,usertype) values(?,?,?,?,0)";            ps=conn.prepareStatement(sql);            ps.setString(1, number);//第一个问号，预处理特殊字符            ps.setString(2, username);//第一个问号，预处理特殊字符            ps.setString(3, password);            ps.setString(4, usergroud);            int i=ps.executeUpdate();            if(i>0){                return true;             }            return false;        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtils.free(rs, ps, conn);        }        return true;    }    //注册时候判断学号是否已经注册过了，学号是唯一的    public static boolean selectByNumber(String number){        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            String sql="select id from user_info where number=?";            ps=conn.prepareStatement(sql);            ps.setString(1, number);//第一个问号，预处理特殊字符            rs=ps.executeQuery();            return rs.next();        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtils.free(rs, ps, conn);        }        return true;    }    //获取个人信息    public static StudentUser selectById(int id){        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        StudentUser stu=null;        try {            conn = JdbcUtils.getConnection();            String sql="select * from user_info where id=?";            ps=conn.prepareStatement(sql);            ps.setInt(1, id);//第一个问号，预处理特殊字符            rs=ps.executeQuery();            stu=new StudentUser();            while(rs.next()){                stu.setChinesegrade(rs.getDouble("chinesegrade"));                stu.setEnglishgrade(rs.getDouble("englishgrade"));                stu.setId(rs.getInt("id"));                stu.setMathgrade(rs.getDouble("mathgrade"));                stu.setNumber(rs.getString("number"));                stu.setPassword(rs.getString("password"));                stu.setUsergroud(rs.getString("usergroud"));                stu.setUsername(rs.getString("username"));                stu.setUsertype(rs.getInt("usertype"));            }            return stu;        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtils.free(rs, ps, conn);        }        return null;    }    //列表搜索,根据人名进行    public static List<StudentUser> selectByName(String username,int usertype,String serachname){        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        List<StudentUser> data=null;        try {            conn = JdbcUtils.getConnection();            String where=" where usertype=?";            if(username.length()>0){                where += " and username=?";            }            if(serachname.length()>0){                where += " and username like ?";            }            String sql="select *,(mathgrade+chinesegrade+englishgrade) as amount from user_info"+where;            ps=conn.prepareStatement(sql);            ps.setInt(1, usertype);//第一个问号，预处理特殊字符            if(username.length()>0){                ps.setString(2, username);            }            if(serachname.length()>0){                ps.setString(2, "%"+serachname+"%");            }            rs=ps.executeQuery();            data=new ArrayList<>();            while(rs.next()){                StudentUser stu=new StudentUser();                stu.setChinesegrade(rs.getDouble("chinesegrade"));                stu.setEnglishgrade(rs.getDouble("englishgrade"));                stu.setId(rs.getInt("id"));                stu.setMathgrade(rs.getDouble("mathgrade"));                stu.setNumber(rs.getString("number"));                stu.setPassword(rs.getString("password"));                stu.setUsergroud(rs.getString("usergroud"));                stu.setUsername(rs.getString("username"));                stu.setUsertype(rs.getInt("usertype"));                stu.setAmount(rs.getDouble("amount"));                data.add(stu);            }            return data;        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtils.free(rs, ps, conn);        }        return null;    }    //录入成绩    public static boolean updateGrade(int id,double math,double chinese,double english){        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            String sql="update user_info set mathgrade=?,englishgrade=?,chinesegrade=? where id=?";            ps=conn.prepareStatement(sql);            ps.setDouble(1, math);            ps.setDouble(2, english);            ps.setDouble(3, chinese);            ps.setInt(4, id);            int i=ps.executeUpdate();            if(i>0){                return true;             }            return false;        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtils.free(rs, ps, conn);        }        return true;    }