JDBC操作mysql数据库(SqlHelper类封装)
SqlHelper.java
package com.mqs.process; import java.io.FileInputStream; import java.sql.*; import java.util.Properties; public class SqlHelper { //定义需要的变量 private static Connection ct=null; //在大多数情况下，使用PreparedStatement来替代Statement //这样可以防止sql注入 private static PreparedStatement ps=null; private static ResultSet rs=null; private static CallableStatement cs=null; public static Connection getCt() { return ct; } public static PreparedStatement getPs() { return ps; } public static ResultSet getRs() { return rs; } //连接数据库参数 private static String url=""; private static String username=""; private static String driver=""; private static String password=""; private static Properties pp=null; private static FileInputStream fis=null; //加载驱动，只需要一次 static{ try { //从dbinfo.propertis文件中读取配置信息 pp = new Properties(); fis=new FileInputStream("dbinfo.properties"); pp.load(fis); url=pp.getProperty("url"); username=pp.getProperty("username"); driver=pp.getProperty("driver"); password=pp.getProperty("password"); Class.forName("driver"); } catch (Exception e) { // TODO: handle exception }finally{ try { fis.close(); } catch (Exception e) { e.printStackTrace(); } fis=null; } } //得到连接 public static Connection getConnection(){ try { ct = DriverManager.getConnection(url,username,password); } catch (Exception e) { // TODO: handle exception } return ct; } //统一的select public static ResultSet executeQuery(String sql,String parameters[]){ try { ct=getConnection(); ps=ct.prepareStatement(sql); if(parameters!=null&&!parameters.equals("")){ for(int i=0;i<parameters.length;i++){ ps.setString(i+1,parameters[i]); } } rs=ps.executeQuery(); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e.getMessage()); }finally{ //close(rs,ps,ct); } return rs; } //先写一个update/insert/delete public static void executeUpdate(String sql,String [] parameters){ //1、创建一个ps try{ ct=getConnection(); ps=ct.prepareStatement(sql); //给？赋值 if(parameters!=null){ for(int i=0;i<parameters.length;i++){ ps.setString(i+1,parameters[i]); } } //执行 ps.executeUpdate(); }catch (Exception e){ e.printStackTrace();//开发阶段 //抛出异常，抛出运行异常，可以给调用该函数的函数一个选择 //可以处理，也可以放弃处理 throw new RuntimeException(e.getMessage()); }finally{ //关闭资源 close(rs,ps,ct); } } //如果有多个update/delete/insert[需要考虑事务] public static void executeUpdate2(String sql[],String [][]parameters){ try { //1、获得链接 ct=getConnection(); //因为这时，用户传入的可能是多个sql语句 ct.setAutoCommit(false); for(int i=0;i<sql.length;i++){ if(parameters[i]!=null){ ps=ct.prepareStatement(sql[i]); for(int j=0;j<parameters[i].length;j++){ ps.setString(j+1, parameters[i][j]); } ps.executeUpdate(); } } ct.commit(); } catch (Exception e) { e.printStackTrace();//开发阶段 //回滚 try { ct.rollback(); } catch (Exception e1) { e1.printStackTrace(); } //抛出异常，抛出运行异常，可以给调用该函数的函数一个选择 //可以处理，也可以放弃处理 throw new RuntimeException(e.getMessage()); }finally{ close(rs,ps,ct); } } //分页问题 public static ResultSet executeQuery2(){ return null; } //调用存储过程 //sql 像{call 过程(?,?,?)} public static void callPro1(String sql,String []parameters){ try { ct=getConnection(); cs=ct.prepareCall(sql); //?号赋值 if(parameters!=null){ for(int i=0;i<parameters.length;i++){ cs.setObject(i+1, parameters[i]); } } cs.execute(); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e.getMessage()); }finally{ close(rs,cs,ct); } } //调用存储过程，有返回Result //未完成，见《韩顺平.2011最新j2ee视频教程.jdbc第4讲.超强SqlHelper工具类封装(2)》 public static ResultSet callPro2(String sql,String []inparameters,String []outparameters){ try { ct=getConnection(); cs=ct.prepareCall(sql); if(inparameters!=null){ for(int i=0;i<inparameters.length;i++){ cs.setObject(i+1,inparameters[i]); } } } catch (Exception e) { // TODO: handle exception }finally{ } return null; } //关闭资源的函数 public static void close(ResultSet rs,Statement ps,Connection ct){ if(rs!=null){ try { rs.close(); } catch (Exception e) { e.printStackTrace(); } rs=null; } if(ps!=null){ try { ps.close(); } catch (Exception e) { e.printStackTrace(); } ps=null; } if(ct!=null){ try { ct.close(); } catch (Exception e) { e.printStackTrace(); } ct=null; } } }
DbTest.java
package com.mqs.process; import java.sql.*; import java.util.ArrayList; public class DbTest { public static void main(String[] args) { Connection ct =null; PreparedStatement ps=null; ResultSet rs=null; try { //1、获取数据库链接 ct=SqlHelper.getConnection(); //2、创建获取rs String sql = "select * from news"; String parameters[]={}; rs=SqlHelper.executeQuery(sql,parameters); while(rs.next()){ System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)); } } catch (Exception e) { e.printStackTrace(); }finally{ if(rs!=null){ try { rs.close(); } catch (Exception e) { e.printStackTrace(); } rs=null; } if(ps!=null){ try { ps.close(); } catch (Exception e) { e.printStackTrace(); } ps=null; } if(ct!=null){ try { ct.close(); } catch (Exception e) { e.printStackTrace(); } ct=null; } } } }
dbinfo.properties
url=jdbc:mysql://127.0.0.1:3306/site?useUnicode=true&characterEncoding=UTF-8 username=root driver=com.mysql.jdbc.Driver password=654321
直接操作数据库的方法
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; Connection conn = null; ResultSet rs = null; String sql = "select * from news"; String url = "jdbc:mysql://127.0.0.1:3306/site?user=root&password=654321&useUnicode=true&characterEncoding=UTF-8"; ArrayList<String[]> news = new ArrayList<String[]>(); try{ Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url); java.sql.Statement stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()){ String[] temp = {rs.getString(1),rs.getString(2),rs.getString(3)}; news.add(temp); } }catch (SQLException e) { System.out.println("MySQL操作错误"); e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
自己写的封装
package com.yourcompany.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DbHelper { private static Connection conn=null; private static PreparedStatement ps=null; private static ResultSet rs = null; private static String url = "jdbc:mysql://127.0.0.1:3306/site?user=root&password=654321&useUnicode=true&characterEncoding=UTF-8"; //private static String url = "jdbc:mysql://127.0.0.1:3306/site"; //获取查询数据 public static ResultSet getQuery(String sql,String parameters[]){ try{ Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url); ps = conn.prepareStatement(sql); if(parameters!=null&&!parameters.equals("")){ for(int i=0;i<parameters.length;i++){ ps.setString(i+1,parameters[i]); } } rs = ps.executeQuery(); }catch (SQLException e) { System.out.println("MySQL操作错误"); e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { } return rs; } //关闭资源的函数 public static void close(){ if(rs!=null){ try { rs.close(); } catch (Exception e) { e.printStackTrace(); } rs=null; } if(ps!=null){ try { ps.close(); } catch (Exception e) { e.printStackTrace(); } ps=null; } if(conn!=null){ try { conn.close(); } catch (Exception e) { e.printStackTrace(); } conn=null; } } }
