java连接mysql工具类_第一天：java与mysql的连接工具类

第一天：java与mysql的连接工具类

java最新版马上就要收费，这无疑是这门语言的衰败起始，毕竟在中国收费便难发展，例如c#，但是毕业设计已经选好用java来写一个动态网站，

这已经是一个事实，还是得学，好在一法通万法通，不至于一无所获。

首先我们要把连接数据库的工具类写好，这里面无非就那么几个固定的对象、语句，

第一步，我们需要导包，进入maven随便选择一个版本下载就是，网址是https://mvnrepository.com/artifact/mysql/mysql-connector-java

然后把jar包复制到java动态网站下的lib文件夹里就是了，位置在/项目名/WebContent/WEB-INF/lib/mysql-connector-java-5.1.24-bin.jar

接下来就可以写连接类了，来个最简单的：

public classJdbc {public static final String URL = "jdbc:mysql://localhost:3306/test";public static final String USER = "root";public static final String PWD = "123456";public static voidmain(String[] args) {//update();

query();

}public static voidupdate() {

Connection conn= null;

Statement stmt=null;

PreparedStatement pstmt= null;

ResultSet rs= null;try{//加载驱动类

Class.forName("com.mysql.jdbc.Driver");//与数据库建立连接

conn =(Connection) DriverManager.getConnection(URL,USER,PWD);//执行sql//1.statement

/*stmt = conn.createStatement();

String sql = "insert into user values(2,'qzj',123) ";

int count = stmt.executeUpdate(sql);*/

//2.prepareStatement

String sql = "insert into user values(?,?,?) ";

pstmt=conn.prepareStatement(sql);

pstmt.setInt(1, 3);

pstmt.setString(2, "gg");

pstmt.setInt(3, 22);int count =pstmt.executeUpdate();if(count > 0) {

System.out.println("操作成功！");

}

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}catch(Exception e) {

e.printStackTrace();

}finally{try{if(stmt != null) stmt.close();if(conn != null) conn.close();

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}public static voidquery() {

Connection conn= null;

Statement stmt=null;

PreparedStatement pstmt= null;

ResultSet rs= null;try{//加载驱动类

Class.forName("com.mysql.jdbc.Driver");//与数据库建立连接

conn =(Connection) DriverManager.getConnection(URL,USER,PWD);//执行sql

String sql = "select * from user";/*stmt = conn.createStatement();

rs = stmt.executeQuery(sql);*/pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();while(rs.next()) {int id = rs.getInt("id");

String name= rs.getString("uname");

String pwd= rs.getString("upwd");

System.out.println(id+"--"+name+"--"+pwd);

}

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}catch(Exception e) {

e.printStackTrace();

}finally{try{if(stmt != null) stmt.close();if(conn != null) conn.close();if(rs != null) rs.close();

}catch(SQLException e) {

e.printStackTrace();

}

}

}

}

View Code

基本上连接类中用到的就connection、statement、preparstatement、resultset四个对象，其中preparstatement是statement的子类，功能更多更好，

推荐优先使用preparstatement，理由如下：

1.编码更加简便(避免了字符串的拼接)

String name = "zs" ;

int age = 23 ;

stmt:

String sql =" insert into student(stuno,stuname) values('"+name+"', "+age+" ) " ;

stmt.executeUpdate(sql);

pstmt:

String sql =" insert into student(stuno,stuname) values(?,?) " ;

pstmt = connection.prepareStatement(sql);//预编译SQL

pstmt.setString(1,name);

pstmt.setInt(2,age);

2.提高性能(因为 有预编译操作，预编译只需要执行一次)

需要重复增加100条数

stmt:

String sql =" insert into student(stuno,stuname) values('"+name+"', "+age+" ) " ;

for(100)

stmt.executeUpdate(sql);

pstmt:

String sql =" insert into student(stuno,stuname) values(?,?) " ;

pstmt = connection.prepareStatement(sql);//预编译SQL

pstmt.setString(1,name);

pstmt.setInt(2,age);

for( 100){

pstmt.executeUpdate();

}

3.安全(可以有效防止sql注入)，何为sql注入，就是--将客户输入的内容  和 开发人员的SQL语句 混为一体

stmt:存在被sql注入的风险

(例如输入  用户名：任意值 ' or 1=1 --

密码：任意值)

分析：

当源代码像select count(*) from login where uname='"+name+"' and upwd ='"+pwd+"'

用户有心捣乱就可以进行sql注入，输入恶意用户名就变成了以下后果：

select count(*) from login where uname='任意值 ' or 1=1 --' and upwd ='任意值'  ;//--是sql中的注释，后面语句被注释了

select count(*) from login where uname='任意值 ' or 1=1 ;//结果就变成了这样，因为or1=1，最终就正确了

select count(*) from login ;

pstmt:有效防止sql注入，推荐使用pstmt

除了以上直接输入连接信息外还可以写在一个/项目名/src/a.properties文件，再从a.properties取出来用，代码如下：

private static String url = null;

private static String user = null;

private static String password = null;

private static String dv = null;

static {

Properties prop = new Properties();

InputStream in = JdbcUtils.class.getResourceAsStream("/a.properties");

try {

prop.load(in);

url = prop.getProperty("url");

user = prop.getProperty("user");

password= prop.getProperty("password");

dv = prop.getProperty("driver");

//加载驱动类

try {

Class.forName(dv);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

a.properties文件语句如下：

url:jdbc:mysql://localhost:3306/diary?characterEncoding=utf8

user:root

password:123456

driver:com.mysql.jdbc.Driver

上面只是最简单的也是最原始的连接工具类，再来个比较高级通用的：

packagejdbc.util;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.sql.Connection;//需要WebContent/WEB-INF/lib/mysql-connector-java-5.1.24-bin.jar

public classJdbcUtil {public static final String URL = "jdbc:mysql://localhost:3306/test";public static final String USER = "root";public static final String PWD = "123456";public static PreparedStatement pstmt = null;public static Connection connection = null;public static ResultSet rs = null;//通用增删改

public static booleanexecuteUpdate(String sql,Object[]params){try{

pstmt=createPreParedStatement(sql,params);int count =pstmt.executeUpdate();if(count>0) {return true;

}else{return false;

}

}catch(ClassNotFoundException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();return false;

}finally{

closeAll(null,pstmt,connection);

}

}//通用查

public staticResultSet executeQuery(String sql,Object[]params) {//Student student = null;//List students = new ArrayList<>();

try{

pstmt=createPreParedStatement(sql,params);

rs=pstmt.executeQuery();returnrs;

}catch(ClassNotFoundException e) {//TODO Auto-generated catch block

e.printStackTrace();return null;

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();return null;

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();return null;

}

}//导入驱动，加载具体的驱动类

public static Connection getConnection() throwsClassNotFoundException, SQLException {

Class.forName("com.mysql.jdbc.Driver");returnDriverManager.getConnection(URL,USER,PWD);

}public static PreparedStatement createPreParedStatement(String sql,Object[] params) throwsClassNotFoundException, SQLException {

pstmt=getConnection().prepareStatement(sql);if(pstmt != null) {for(int i=0;i

pstmt.setObject(i+1, params[i]);

}

}returnpstmt;

}public static voidcloseAll(ResultSet rs,Statement stmt,Connection connection)

{try{if(rs!=null)rs.close();if(pstmt!=null)pstmt.close();if(connection!=null)connection.close();

}catch(SQLException e) {

e.printStackTrace();

}

}

}

View Code