package msycrud;
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 java.util.List;
import java.util.Scanner;
private Connection conn = null;
private PreparedStatement pstm = null;
private ResultSet rs = null;
private String ip;
private int portNumber;
private String db_name;
private String userName;
private String userPass;
public sqlCRUD(String ip, int portNumber, String db_name, String userName, String userPass) {
this.ip = ip;
this.portNumber = portNumber;
this.db_name = db_name;
this.userName = userName;
this.userPass = userPass;
}
static {
// 1.注册数据库驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取数据库连接
*
* @throws SQLException
*/
private void getConnection() throws SQLException {
conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + portNumber + "/" + db_name, userName, userPass);
}
/**
* 更新数据库
*
* @param sql
* 要执行的sql语句
* @param values
* sql语句的占位符的值
* @throws SQLException
*/
public void executeUpdate(String sql, Object... values) throws SQLException {
// 获取数据库连接
getConnection();
// 执行sql并返回结果
pstm = conn.prepareStatement(sql);
setParameter(values);
int row = pstm.executeUpdate();
// 判断是否更新成功
judgment(row);
}
/**
* 对数据库的查询
*
* @param sql
* 要执行的sql语句
* @param values
* sql语句的占位符的值
* @return
* @throws SQLException
*/
public void executeQuery(String sql, Object... values) throws SQLException {
// 获取数据库连接
getConnection();
// 执行sql并返回结果
pstm = conn.prepareStatement(sql);
setParameter(values);
rs = pstm.executeQuery();
}
/**
* 为sql语句的占位符赋值
*
* @param values
* sql语句的占位符的值
* @throws SQLException
*/
private void setParameter(Object... values) throws SQLException {
if (values != null) {
for (int i = 0; i < values.length; i++) {
pstm.setObject(i + 1, values[i]);
}
}
}
/**
* 判断是否更新成功
*
* @param row
* 判断更新是否成功的条件值
*/
private void judgment(int row) {
if (row > 0) {
System.out.println("成功!");
} else {
System.out.println("失败!");
}
}
/**
* 查询结果
*
* @throws SQLException
*/
public void QueryResult(String[] columnName) throws SQLException {
while (rs.next()) {
for (int i = 0; i < columnName.length; i++) {
System.out.print(rs.getString(columnName[i])+"\t");
}
System.out.println();
}
}
/**
* 关闭资源
*
* @throws SQLException
*/
@SuppressWarnings("unused")
private void close() throws SQLException {
if (rs != null)
rs.close();
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
}
}
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 java.util.List;
import java.util.Scanner;
使用时需要先导入mysql的jar包
public class sqlCRUD {
// 实例化连接对象private Connection conn = null;
private PreparedStatement pstm = null;
private ResultSet rs = null;
private String ip;
private int portNumber;
private String db_name;
private String userName;
private String userPass;
public sqlCRUD(String ip, int portNumber, String db_name, String userName, String userPass) {
this.ip = ip;
this.portNumber = portNumber;
this.db_name = db_name;
this.userName = userName;
this.userPass = userPass;
}
static {
// 1.注册数据库驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取数据库连接
*
* @throws SQLException
*/
private void getConnection() throws SQLException {
conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + portNumber + "/" + db_name, userName, userPass);
}
/**
* 更新数据库
*
* @param sql
* 要执行的sql语句
* @param values
* sql语句的占位符的值
* @throws SQLException
*/
public void executeUpdate(String sql, Object... values) throws SQLException {
// 获取数据库连接
getConnection();
// 执行sql并返回结果
pstm = conn.prepareStatement(sql);
setParameter(values);
int row = pstm.executeUpdate();
// 判断是否更新成功
judgment(row);
}
/**
* 对数据库的查询
*
* @param sql
* 要执行的sql语句
* @param values
* sql语句的占位符的值
* @return
* @throws SQLException
*/
public void executeQuery(String sql, Object... values) throws SQLException {
// 获取数据库连接
getConnection();
// 执行sql并返回结果
pstm = conn.prepareStatement(sql);
setParameter(values);
rs = pstm.executeQuery();
}
/**
* 为sql语句的占位符赋值
*
* @param values
* sql语句的占位符的值
* @throws SQLException
*/
private void setParameter(Object... values) throws SQLException {
if (values != null) {
for (int i = 0; i < values.length; i++) {
pstm.setObject(i + 1, values[i]);
}
}
}
/**
* 判断是否更新成功
*
* @param row
* 判断更新是否成功的条件值
*/
private void judgment(int row) {
if (row > 0) {
System.out.println("成功!");
} else {
System.out.println("失败!");
}
}
/**
* 查询结果
*
* @throws SQLException
*/
public void QueryResult(String[] columnName) throws SQLException {
while (rs.next()) {
for (int i = 0; i < columnName.length; i++) {
System.out.print(rs.getString(columnName[i])+"\t");
}
System.out.println();
}
}
/**
* 关闭资源
*
* @throws SQLException
*/
@SuppressWarnings("unused")
private void close() throws SQLException {
if (rs != null)
rs.close();
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
}
}