连接mysql的java按条件修改语句_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);

// booleanlast() 将光标移动到此 ResultSet 对象的最后一行。

resultSet.last();

// booleanlast() 将光标移动到此 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);

}

}