Question

请问如何对用Java对mysql进行增删改查等方法？

我是新手刚学会使用Java，现在还不熟练，请哪位大哥能帮我写个增删改查的方法，在同一个类里面写，要求要有数据库连接，和增删改查的方法！谢谢了！我用的是mysql数据库！谢谢了！

Answer 1

package com.ly520.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
* mysql数据库操作类。
*
* @author Ryoma
*
*/
public class MySqlOperate implements SqlOperate {

private Connection con;
private String DBname;
private String sql;

/**
* Mysql数据库初始化包名
*/
private String INIT_SQL_CTX = "org.gjt.mm.mysql.Driver";
/**
* MYSQL数据库服务参数:服务器IP地址和端口
*/
private String SQL_SERVICE = "jdbc:mysql://127.0.0.1:3306?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8";
/**
* MYSQL数据库参数:系统管理用户
*/
private String SQL_SYSTEM_USER = "root";
/**
* MYSQL数据库参数:系统管理密码
*/
private String SQL_SYSTEM_PASSWORD = "123456";

/**
* 初始化链接方法。此方法不需要再使用use Dbname;
*/
public MySqlOperate() {
try {
Context ctx = new InitialContext();
if (ctx == null)
throw new Exception("Boom - No Context");
// java:comp/env/jdbc/imabled_mysql 为配置的连接池的jndi名称。
DataSource ds = (DataSource) ctx
.lookup("java:comp/env/jdbc/imabled_mysql");
con = ds.getConnection();
} catch (Exception ex) {
ex.printStackTrace();
}
}

/**
* 指定库名的连接方法，此方法没有使用连接池，不赞成使用。
*
* @param DBn
*/
public MySqlOperate(String DBn) {
try {
Class.forName(this.INIT_SQL_CTX);

} catch (java.lang.ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(this.SQL_SERVICE + "/" + DBn,
this.SQL_SYSTEM_USER, this.SQL_SYSTEM_PASSWORD);
DBname = DBn;
} catch (SQLException ex) {
ex.printStackTrace();
}
}

public boolean add(String tablename, Hashtable searchdetail) {
Statement stmt = null;
Enumeration key_values = searchdetail.keys();
String key, value;
String temp = (String) key_values.nextElement();
key = temp;
value = "'" + searchdetail.get(temp) + "'";
while (key_values.hasMoreElements()) {
temp = (String) key_values.nextElement();
key = key + "," + temp;
value = value + "," + "'" + searchdetail.get(temp) + "'";
}
try {
useDB();
stmt = con.createStatement();
sql = "insert into " + tablename + " (" + key + ") " + " VALUES ("
+ value + ")";
stmt.executeUpdate(sql);
return true;

} catch (SQLException ex) {
System.out.println("执行的出错的sql语句:" + sql);
ex.printStackTrace();
return false;
} finally {
closeStmt(stmt);
}
}

public boolean batchImport(String tablename, List list, String[] fields)
throws SQLException {
PreparedStatement ps = null;
String key, value;
key = toString(fields);
String[] values = new String[fields.length];
for (int i = 0; i < values.length; i++) {
values[i] = "?";
}
value = toString(values);
try {
useDB();
con.setAutoCommit(false);
System.out.println("insert into " + tablename + " (" + key + ") "
+ " VALUES (" + value + ")");
ps = con.prepareStatement("insert into " + tablename + " (" + key
+ ") " + " VALUES (" + value + ")");
String[] tmpData = null;
for (int i = 0; i < list.size(); i++) {
tmpData = (String[]) list.get(i);
for (int j = 0; j < fields.length && j < tmpData.length; j++) {
ps.setString(j + 1, tmpData[j]);
}
ps.addBatch();// 添加执行的语句。
}
int[] count = ps.executeBatch();// 批量执行
con.commit();
return true;
} catch (SQLException ex) {
throw ex;
} finally {
try {
if (ps != null) {
ps.clearParameters();
ps.close();
ps = null;
}
} catch (SQLException e) {
throw e;
}
}
}

public boolean delete(String tablename, String filter) {
Statement stmt = null;
String value;
try {
useDB();
stmt = con.createStatement();
sql = "delete from " + tablename + " where " + filter;
stmt.executeUpdate(sql);
return true;
} catch (SQLException e) {
System.out.println("执行的出错的sql语句:" + sql);
e.printStackTrace();
return false;
} finally {
closeStmt(stmt);
}
}

public Hashtable list(String sql) {
Hashtable[] infoList = search(sql);
if (infoList == null || infoList.length < 1)
return null;
return infoList[0];
}

public Hashtable list(String tablename, String id) {
String sql = "select * from " + tablename + " where id ='" + id + "'";
return list(sql);
}

public boolean modify(String tablename, Hashtable setdetail, String filter) {
Enumeration key_values = setdetail.keys();
Statement stmt = null;
String value;
String temp = (String) key_values.nextElement();
value = temp + "='" + setdetail.get(temp) + "'";
while (key_values.hasMoreElements()) {
temp = (String) key_values.nextElement();
value = value + "," + temp + "='" + setdetail.get(temp) + "'";
}
try {
useDB();
stmt = con.createStatement();
sql = "update " + tablename + " set " + value + " where " + filter;
int tag = stmt.executeUpdate(sql);
if (tag == 0)
return false;
else
return true;

} catch (SQLException e) {
System.out.println("执行的出错的sql语句:" + sql);
e.printStackTrace();
return false;
} finally {
closeStmt(stmt);
}
}

public Hashtable[] search(String sql) {
ResultSet rs;
Statement stmt = null;
try {
useDB();
stmt = con.createStatement();
rs = stmt.executeQuery(sql);

return toHashtableArray(rs);
} catch (SQLException ex) {
System.out.println("执行的出错的sql语句:" + sql);
ex.printStackTrace();
return null;
} finally {
closeStmt(stmt);
}
}

public Hashtable[] search(String tablename, String[] fieldname,
String filter) {
return search(tablename, fieldname, filter, "");
}

public Hashtable[] search(String tablename, String[] fieldname,
String filter, String ordergroup) {
ResultSet rs;
Statement stmt = null;
String colname = fieldname[0];

for (int i = 1; i < fieldname.length; i++) {
colname += "," + fieldname[i];
}
String queryString = "select " + colname + " from " + tablename;
if (!filter.equals("")) {
queryString = queryString + " where " + filter;
}
if (!ordergroup.equals("")) {
queryString = queryString + " " + ordergroup;
}
return search(sql);
}

/**
* @return the con
*/
public Connection getCon() {
return con;
}

public void close() {
try {
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* 返回使用的数据库
*
* @return the dBname
*/
public String getDBname() {
return DBname;
}

/**
* 设置使用的数据库
*
* @param bname
* the dBname to set
*/
public void setDBname(String bname) {
DBname = bname;
}

/**
* 返回执行的sql语句
*
* @return the sql
*/
public String getSql() {
return sql;
}

/**
* 本方法是为了再没有使用连接池的情况下，首先选择使用的数据库。
*/
private void useDB() {
if (DBname != null && DBname.equals("")) {
String query = "use " + DBname;
Statement stmt = null;
try {
stmt = con.createStatement();
stmt.execute(query);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}
}

/**
* 关闭Statement
*
* @param stmt
*/
private void closeStmt(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* @param ss
* @return
*/
private String toString(String[] ss) {
String s = "";
for (int i = 0; i < ss.length; i++) {
s += ss[i] + ",";
}
if (s.endsWith(","))
s = s.substring(0, s.length() - 1);
return s;
}

/**
* 把ResultSet转换成Hashtable数组 java.util.Arrays.asList 可以把数组转换成List
*
* @param rs
* @return
*/
private Hashtable[] toHashtableArray(ResultSet rs) {
Vector searchresult = new Vector();
try {
ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
while (rs.next()) {
Hashtable onerow = new Hashtable();
for (int i = 1; i <= column; i++) {
try {
String columnName = rsmd.getColumnName(i);
String columnValue = rs.getString(columnName);
onerow.put(columnName, columnValue);
} catch (Exception e) {
}
}
searchresult.add(onerow);
}
Hashtable[] searchset = new Hashtable[searchresult.size()];
searchresult.toArray(searchset);
return searchset;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
}

Answer 2

先去挣点分再悬赏 这样会有很多人来给你写的