生成JDBCUtils工具类
import java.sql.*;
public class JDBCUtils {
// 驱动
String driver = "com.mysql.jdbc.Driver";
// 连接网址端口
String url= "jdbc:mysql://hadoop102:3306/test?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user = "root";
String password = "123456";
public Connection conn;
public JDBCUtils() {
try {
Class.forName(driver);// 加载驱动程序
conn = (Connection) DriverManager.getConnection(url, user, password);// 连续数据库
if(!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
} catch (Exception e) {
e.printStackTrace();
}
}
// 关闭资源
public void close(Connection conn,PreparedStatement preStmt,Statement statement,ResultSet rs) {
try {
if(conn!=null){
conn.close();
}
if (preStmt!=null){
preStmt.close();
}
if (statement!=null){
statement.close();
}
if (rs!=null){
rs.close();
}
} catch (Exception e) {
System.err.println("----------------- 资源关闭失败! -----------------");
e.printStackTrace();
}
}
}
创建增删改查类
import radomlogTxt.com.pb.utils.JDBCUtils;
import java.sql.*;
public class JavaMySQLJdbcServices {
JDBCUtils db = new JDBCUtils();
PreparedStatement preStmt;
Statement statement;
ResultSet rs;
public static void main(String[] args){
JavaMySQLJdbcServices javaMySQLJdbcServices = new JavaMySQLJdbcServices();
// javaMySQLJdbcServices.add("student","6","LRF","F");
// javaMySQLJdbcServices.update("student","6","name='JN'","gender='M'");
javaMySQLJdbcServices.del("student","4");
javaMySQLJdbcServices.show("student","id","name","gender");
// 关闭资源
javaMySQLJdbcServices.db.close(javaMySQLJdbcServices.db.conn,
javaMySQLJdbcServices.preStmt,
javaMySQLJdbcServices.statement,
javaMySQLJdbcServices.rs);
}
//插入操作
/**
* tableName 表名
* args 增添的字段,(所有字段)
*/
public int add(String tableName,String ... args) {
int i = 0;
String value = "(";
for (int j = 0; j < args.length; j++) {
if (j == args.length - 1) {
value = value + "'" + args[j] + "'" + ")";
break;
}
value = value + "'" + args[j] + "'" + ",";
}
String sql="insert into "+ tableName +" values " + value;
try {
preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
/*preStmt.setString(1, uname);
preStmt.setString(2, uemail);
preStmt.setString(3, upwd);*/
preStmt.executeUpdate();
//Statement statement = (Statement) db.conn.createStatement();
//statement.executeUpdate(sql);
} catch (Exception e) {
System.err.println("--------- 插入失败 ---------");
e.printStackTrace();
}
//返回影响的行数,1为执行成功
return i;
}
//查找操作
/**
* tableName 表名
* num增添的字段,(所有字段)
*/
public void show(String tableName,String ... num){
String sql ="select * from " + tableName;
System.out.println("---------------------------------------------------");
try {
statement = (Statement) db.conn.createStatement();
rs = (ResultSet) statement.executeQuery(sql);
while(rs.next()){
String out = "";
for (int i = 0; i < num.length; i++) {
out += (rs.getString(num[i]) + "\t");
}
System.out.println(out);
}
} catch (SQLException e) {
System.err.println("--------- 查询失败 ---------");
e.printStackTrace();
}
}
//更新操作
/**
* tableName 表名
* id 序号,编号
* args 增添的字段,(修改的字段) 例如: "name='LiShi'"
*/
public int update(String tableName,String id,String ... args) {
int i =0;
String value = "";
for (int j = 0; j < args.length; j++) {
if (j == args.length - 1) {
value +=args[j];
break;
}
value += args[j] + ",";
}
String sql="update "+ tableName +" set "+ value +" where id=" + id;
try {
preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
preStmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
//返回影响的行数,1为执行成功
return i;
}
//删除操作
/**
* tableName 表名
* id 序号,编号
*/
public int del(String tableName,String id) {
int i=0;
String sql="delete from "+ tableName +" where id=" + id;
try {
preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
preStmt.executeUpdate();
} catch (SQLException e){
e.printStackTrace();
}
return i;//返回影响的行数,1为执行成功
}
}