java数据库访问优化（mysql为例）

 2.编写DBUtil类

主要实现数据库连接，释放资源，查询，增删改操作。
代码如下：
 

  package practice;
    import java.sql.*;
    public class DBUtil {
            Connection conn=null;
            PreparedStatement pstmt=null;
            ResultSet rs=null;
            /*得到数据库连接*/
            public Connection getConnection() throws Exception{
                String driver=Config.getValue("driver");
                String url=Config.getValue("url");
                String user=Config.getValue("user");
                String pwd=Config.getValue("password");
                System.out.println("driver :"+driver);
                System.out.println("url :"+url);
                System.out.println("user :"+user);
                System.out.println("pwd :"+pwd);
                try{
                    Class.forName(driver);
                    conn=DriverManager.getConnection(url,user,pwd);
                    return conn;
                }catch(Exception e) {
                    throw new SQLException("驱动错误或连接错误!");
                }
                
            }
            /*释放资源*/
            public void closeAll() {
                if(rs!=null) {
                    try {
                        rs.close();
                    }catch(Exception e) {
                        e.printStackTrace();
                    }
                }
                if(pstmt!=null) {
                    try {
                        pstmt.close();
                    }catch(Exception e) {
                        e.printStackTrace();
                    }
                }
                if(conn!=null) {
                    try {
                        conn.close();
                    }catch(Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            /*执行SQL语句,可以进行查询*/
            public ResultSet executeQuery(String preparedSql ,String[] param) {
                try {
                    pstmt=conn.prepareStatement(preparedSql);
                    if(param!=null) {
                        for (int i=0;i<param.length;i++) {
                            pstmt.setString(i+1,param[i]);
                        }
                    }
                    rs=pstmt.executeQuery();
                    
                }catch(Exception e) {
                    e.printStackTrace();
                }
                return rs;
            }
            /*执行SQL语句,可以进行赠，删，改的操作，不能执行查询*/
            public int executeUpdate (String prepareSql , String[] param) {
                int num=0;
                try {
                    pstmt=conn.prepareStatement(prepareSql);
                    if(param!=null)
                    {
                        for (int i=0;i<param.length;i++) {
                            pstmt.setString(i+1,param[i]);
                        }
                    }
                    num=pstmt.executeUpdate();
                }catch(Exception e) {
                    e.printStackTrace();
                }
                return num;
            }
    }

3.使用DBUtil类
代码如下：

  

 package practice;
    import java.sql.*;
     
    public class DBDmo {
        public static void main(String args[]) {
            String selectSql="select id,username,password,sex from userdetails";
            String insertSql="insert into userdetails(id,username,password,sex) values(?,?,?,?)";
            String updateSql="update userdetails set password=? where username=?";
            String deleteSql="delete from userdetails where username=?";
            DBUtil db=new DBUtil();
            try {
                //连接数据库
                db.getConnection();
                //查询并显示原来的数据
                ResultSet rs=db.executeQuery(selectSql, null);
                System.out.println("----原来数据----");
                while(rs.next()) {
                    System.out.println("行" +rs.getRow() +":" + rs.getInt(1)+ "\t"
                +rs.getString(2)+"\t"+rs.getString(3)+"\t" +(rs.getInt(4)==1? "男":"女"));
                }
                System.out.println("------------");
                //执行添加
                int count=db.executeUpdate(insertSql, new String[] {"9","Rose","123456","0"});
                System.out.println("添加"+count+"行!");
                count=db.executeUpdate(updateSql, new String[] {"686868","Tom"});
                System.out.println("修改"+count+"行！");
                //执行删除
                count=db.executeUpdate(deleteSql,new String[] {"lisi"});
                System.out.println("删除"+count+"行!");
                //查询并显示更新后的数据
                rs=db.executeQuery(selectSql, null);
                System.out.println("----更新后的数据-----");
                while(rs.next())
                {
                    System.out.println("行"+rs.getRow()+":"+rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"
                            +(rs.getInt(4)==1? "男":"女"));
                }
                System.out.println("---------\n");
            }catch(Exception e) {
                e.printStackTrace();
            }finally {
                db.closeAll();
            }
        }
    }

java数据库访问优化（mysql为例）

标签：imp   password   .exe   on()   dstat   使用   out   rest   query