以前用过一些,但总觉得不好用,老容易出错,于是自己在写程序的过程中总结经验,反复修改,写了这一个标准操作,于是mysql用起来终于顺手了
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* MySQL操作类
*
* @author Administrator
*
*/
public class MySQL {
private Connection conn = null;
private Statement stat;
private String connectionString;
private String driver = "org.gjt.mm.mysql.Driver";
/**
* 构造函数
*
* @param connString
* 连接字符串
*/
public MySQL(String connString) {
this.connectionString = connString;
try {
Class.forName(driver);
conn = DriverManager.getConnection(this.connectionString);
} catch (ClassNotFoundException e) {
System.out.println("Class Not Founde=!");
System.out.println("Error : " + e.toString());
} catch (SQLException e) {
System.out.println("Sql Exception!");
System.out.println("Exception : " + e.toString());
}
}
/**
* 构造函数
*
* @param server
* @param port
* @param dbname
* @param user
* @param pass
*/
public MySQL(String server, String port, String dbname, String user,
String pass) {
String connString = "jdbc:mysql://" + server + ":" + port + "/"
+ dbname + "?user=" + user + "&password=" + pass
+ "&useUnicode=true&characterEncoding=UTF-8";
this.connectionString = connString;
try {
Class.forName(driver);
conn = DriverManager.getConnection(this.connectionString);
} catch (ClassNotFoundException e) {
System.out.println("Class Not Founde=!");
System.out.println("Error : " + e.toString());
} catch (SQLException e) {
System.out.println("Sql Exception!");
System.out.println("Exception : " + e.toString());
}
}
/**
* 获得连接
*
* @return
*/
public Connection getConnection() {
return conn;
}
/*
* 关闭连接
*/
public void close() {
try {
if (this.stat != null) {
this.stat.close();
}
if (this.conn != null) {
this.conn.close();
}
} catch (SQLException e) {
System.out.println("Close DB Exception!");
System.out.println("Error : " + e.toString());
}
}
/**
* 执行查询SQL语句
*
* @param sql
* @return
*/
public ResultSet executeQuery(String sql) {
try {
this.stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
return this.stat.executeQuery(sql);
} catch (SQLException e) {
System.out.println("Query Sql Exception!");
System.out.println("Error : " + e.toString());
}
return null;
}
/**
* 执行更新语句
*
* @param sql
*/
public void executeUpdate(String sql) {
try {
this.stat = conn.createStatement();
this.stat.executeUpdate(sql);
} catch (SQLException e) {
if (!(e.getMessage().split(" ")[0].equals("Duplicate"))) {
System.out.println("Update Sql Exception!");
System.out.println("Exception : " + e.toString());
}
}
}
}
dbo类:
我把MySql的标准操作放在上面的MySQL类中,在程序中具体的查询封装在DBO类中,意为数据库操作类(database operations),把程序的对数据库的调用和mysql的基本操作分开,目的使得程序的思路清晰,同时方便调试,也利于代码的复用
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
*
* @author Marin3000
*
*/
public class DBO {
private MySQL db;
/**
* 构造函数,读取配置文件
*/
public DBO() {
String server = "127.0.0.1";
String port = "3306";
String dbname = "users";
String user = "root";
String pass = "password";
this.db = new MySQL(server, port, dbname, user, pass);
}
/**
* 关闭数据库操作
*/
public void close() {
this.db.close();
}
/**
* Select 操作范例
*
* @param status
* @return
*/
public ArrayList<String> getUsers(int status) {
ArrayList<String> result = new ArrayList<String>();
try {
String sql = "SELECT * FROM users WHERE enable=" + status;
ResultSet rs = this.db.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
result.add(name);
}
// 注意rs使用完后要记得关闭,否则会在多次操作后占用大量内存,然后java虚拟机就会报内存不够错误而停止,切记
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* Insert 操作范例
*
* @param name
* @param id
*/
public void saveUser(String name, String email) {
String sql = "INSERT INTO users (name,email) VALUES('" + name + "','"
+ email + "' ) ";
this.db.executeUpdate(sql);
}
/**
* Update 操作范例
*
* @param id
* @param status
*/
public void saveStatus(int id, int status) {
String sql = "UPDATE users SET enable=" + status + " WHERE id=" + id;
this.db.executeUpdate(sql);
}
/**
* 获得指定表的长度
*
* @param tableName
* @return
*/
public int getTableLength(String tableName) {
int length = 0;
try {
String sql = "select count(*) from " + tableName;
ResultSet rs = this.db.executeQuery(sql);
rs.next();
length = rs.getInt("count(*)");
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return length;
}
}
其实在我写的代码中,最初是把Mysql的连接写在程序里,如上面的构造方法中,后来由于不方便修改,比如服务器换ip。换密码之类的,又要重新改写源码,后来就另写了一个类来读写XML文件,所有的Mysql连接的参数自然就放在了XML文件中,这样以后要修改就方便多了,然后上面的构造函数就变成了这样,具体读取XML文件的方法参见我的另一篇博客 java读取XML配置文件
/**
* 构造函数,读取配置文件
*/
public DBO() {
Config conf = XMLReader.loadconfig();
this.db = new MySQL(conf.getConnString());
}
调用的类
这样把mysql的基本操作和具体程序的操作分成两个类后,代码就变得清晰易懂,需要增加新的方法,就在DBO类中增加就可以,方便又不易出错
/**
*
* @author Martin3000
*
*
*/
public class Main {
/**
* @param args
*/
public static void main(String[] args) {
DBO dbo = new DBO();
int length = dbo.getTableLength("users");
dbo.saveUser("martin", "martin.nclab@gmail.com");
dbo.close();
}
}