Java连接MySQL数据库，并进行增删改查
1、具体的代码实现
import java.sql.*;
public class DatabaseService {
/**
* Create Connection
*
* @param dbtype
* @param username
* @param password
* @param url
* @return
* @throws Exception
*/
public static Connection connectDBDriver(String dbtype, String username,
String password, String url) throws Exception {
Connection conn = null;
try {
if (dbtype.equals("mysql")) {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} else if (dbtype.equals("oracle")) {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} else {
}
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* close DB
*
* @param conn
* @throws Exception
*/
public void closeDBDriver(Connection conn) throws Exception {
try {
conn.close();
} catch (Exception e) { /* ignore close errors */
e.printStackTrace();
}
}
/**
* get ResultSet
*
* @param conn
* @param sql
* @return
* @throws Exception
*/
private static ResultSet getResultSet(Connection conn, String sql)
throws Exception {
ResultSet resultSet = null;
try {
// PreparedStatement pstmt;
// ResultSet rset;
//任意的前后滚动;设置为只读类型的参数
Statement statement = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// pstmt = conn.prepareStatement(sql);
resultSet = statement.executeQuery(sql);
} catch (Exception e) {
e.printStackTrace();
}
return resultSet;
}
/**
* get ColumnCount
*
* @param resultSet
* @return
* @throws Exception
*/
private static int getColumnCount(ResultSet resultSet) throws Exception {
int columnCount = 0;
try {
// ResultSet resultSet = this.getResultSet(conn, sql);
//getMetaData() 获取此 ResultSet 对象的列的编号、类型和属性
//getColumnCount() 返回此 ResultSet 对象中的列数。
columnCount = resultSet.getMetaData().getColumnCount();
if (columnCount == 0) {
}
} catch (Exception e) {
e.printStackTrace();
}
return columnCount;
}
/**
* get ColumnCount
*
* @param conn
* @param sql
* @return
* @throws Exception
*/
public static int getColumnCount(Connection conn, String sql) throws Exception {
int columnCount = 0;
try {
// ResultSet resultSet = this.getResultSet(conn, sql);
columnCount = getResultSet(conn, sql).getMetaData()
.getColumnCount();
if (columnCount == 0) {
}
} catch (Exception e) {
e.printStackTrace();
}
return columnCount;
}
/**
* get RowCount
*
* @param conn
* @param sql
* @return
* @throws Exception
*/
public int getRowCount(Connection conn, String sql) throws Exception {
int rowCount = 0;
try {
ResultSet resultSet = getResultSet(conn, sql);
// boolean last() 将光标移动到此 ResultSet 对象的最后一行。
resultSet.last();
// boolean last() 将光标移动到此 ResultSet 对象的最后一行。
rowCount = resultSet.getRow();
if (rowCount == 0) {
}
} catch (Exception e) {
e.printStackTrace();
}
return rowCount;
}
/**
* get RowCount
*
* @param resultSet
* @return
* @throws Exception
*/
private static int getRowCount(ResultSet resultSet) throws Exception {
int rowCount = 0;
try {
resultSet.last();
rowCount = resultSet.getRow();
if (rowCount == 0) {
}
} catch (Exception e) {
e.printStackTrace();
}
return rowCount;
}
/**
* get data by row index and col index
*
* @param conn
* @param sql
* @param row
* @param col
* @return
* @throws Exception
*/
public static String getData(Connection conn, String sql, int row, int col)
throws Exception {
String data = null;
int rownum = 0;
int rowcount = 0;
int colcount = 0;
try {
ResultSet resultSet = getResultSet(conn, sql);
colcount = getColumnCount(resultSet);
rowcount = getRowCount(resultSet);
//beforeFirst() 将光标移动到此 ResultSet 对象的开头，正好位于第一行之前。
resultSet.beforeFirst();
if (rowcount > 0) {
if (row <= 0 || row > rowcount) {
} else {
if (col <= 0 || col > colcount) {
} else {
while (resultSet.next()) {
rownum++;
if (rownum == row) {
data = resultSet.getString(col);
break;
}
}
}
}
} else {
}
} catch (Exception e) {
e.printStackTrace();
}
return data;
}
/**
* get data by row index and col index
*
* @param conn
* @param sql
* @param row
* @param field
* @return
* @throws Exception
*/
public static String getData(Connection conn, String sql, int row, String field)
throws Exception {
String data = null;
int rownum = 0;
int rowcount = 0;
// int colcount = 0;
try {
ResultSet resultSet = getResultSet(conn, sql);
// colcount = getColumnCount(resultSet);
rowcount = getRowCount(resultSet);
resultSet.beforeFirst();
if (rowcount > 0) {
if (row <= 0 || row > rowcount) {
} else {
while (resultSet.next()) {
rownum++;
if (rownum == row) {
data = resultSet.getString(field);
break;
}
}
}
} else {
}
} catch (Exception e) {
e.printStackTrace();
}
return data;
}
// executeUpdate方法可以执行新增、更新、删除三种sql语句
public static int executeUpdate(Connection conn, String sql) {
Statement stmt = null;
try {
stmt = conn.createStatement();
stmt.executeUpdate(sql);
int updateCount = stmt.getUpdateCount();
return updateCount;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return 0;
}
}
2、调用函数实现增删改查
public class JdbcData {
public static void main(String[] args) {
Connection connection = databaseService.connectDBDriver("mysql","username","password","URL");
String sql = "XXXX'";
//查询数据，获取到数据
String data = DatabaseService.getData(connection, sql,1,1);
String sqlstatus = "XXXX";
//update数据
DatabaseService.executeUpdate(connection,sqlstatus);
}
}
