JDBC连接mysql 数据库
下面截图是mysql数据库的表结构
package com.rmq.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.rmq.bean.Boos;
import com.rmq.bean.Employee;
public class JDBC {
private static String url = “jdbc:mysql://localhost:3306/employee?characterEncoding=UTF8”;
private static String user = “root”;
private static String pass = “root”;
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet rs=null;
private static PreparedStatement ps=null;
static {
try {
Class.forName(“com.mysql.jdbc.Driver”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
if (conn == null || conn.isClosed()) {
conn = DriverManager.getConnection(url, user, pass);
}
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static int executeDML(String sql) {
int result = 0;
try {
conn = getConnection();
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
/**
-
查询操作,此处不能进行资源释放,因为要返回rs,后期可以考虑用ArrayList进行封装返回
-
author:rmq
-
@param sql
-
@param params
-
@return ResultSet
*/
public static ArrayList executeQuerysu(String sql,String[] params) {
ArrayList al=new ArrayList();
try {
conn=getConnection();
ps=conn.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
ps.setString(i+1, params[i]);
}
rs = ps.executeQuery();
while(rs.next()) {
Employee emp=new Employee();
emp.setEid(rs.getInt(“eid”));
emp.setEname(rs.getString(“ename”));
emp.setLname(rs.getString(“lname”));
emp.setAge(rs.getInt(“age”));
emp.setWages(rs.getString(“wages”));
emp.setAddress(rs.getString(“address”));al.add(emp); }
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
release();
}
return al;
}
/**
-
查询操作,此处不能进行资源释放,因为要返回rs,后期可以考虑用ArrayList进行封装返回
-
author:rmq
-
@param sql
-
@param params
-
@return ResultSet
*/
public static ArrayList executeQuerys(String sql,String[] params) {
ArrayList al=new ArrayList();
try {
conn=getConnection();
ps=conn.prepareStatement(sql);
for(int i=0;i<params.length;i++) {
ps.setString(i+1, params[i]);
}
rs = ps.executeQuery();
while(rs.next()) {
Boos boos=new Boos();
boos.setBoosname(rs.getString(“boosname”));
boos.setBoosspw(rs.getString(“boosspw”));
al.add(boos);
}} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
release();
}
return al;
}
/**
- 实现数据的增、删、改的操作
- author:rmq
- @param sql
- @param params
- @return num
*/
public static int executeUpdate(String sql,String[] params) {
int num=0;
try {
conn=getConnection();
ps=conn.prepareStatement(sql);
for(int i=0;i<params.length;i++){
ps.setString(i+1, params[i]);
}
num=ps.executeUpdate();//方法executeUpdate的返回值为一个整数,若执行成功则返回值>0,若执行失败则返回值=0
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
release();
}
return num;
}
/**
-
资源释放
- authorrmq
- @throws SQLException
*/
public static void release() {
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(stmt!=null)
try {
stmt.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();
}
}
}
写的不好,希望能帮到你