mysql-java笔记

<pre name="code" class="java">import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;public class Test {private static Connection conn;public static void connect() throws SQLException{conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "danding");}public static void listall(String tableName) throws SQLException {Statement statement = conn.createStatement();statement.execute("select* from "+tableName);ResultSet res = statement.getResultSet();ResultSetMetaData metadata = res.getMetaData();int n=metadata.getColumnCount();for (int i = 1; i <= n; i++) {System.out.print(metadata.getColumnName(i)+"\t");}System.out.println();while (res.next()) {for (int i = 1; i <= n; i++) {System.out.print(res.getObject(i)+"\t");}System.out.println();}}public ResultSet getResultSet(String query) throws SQLException{Statement statement = conn.createStatement();statement.execute(query);ResultSet res = statement.getResultSet();return res;}public void insertIFNotExists() throws SQLException{PreparedStatement preparedStatement = conn.prepareStatement("insert into s select ?,?  from s "+ "where not exists (select* from s where id=?)");preparedStatement.setInt(1, 2);preparedStatement.setString(2,"李四");preparedStatement.setInt(3, 2);preparedStatement.addBatch();preparedStatement.setInt(1, 3);preparedStatement.setString(2,"王五");preparedStatement.setInt(3, 3);preparedStatement.addBatch();preparedStatement.executeBatch();}public boolean checkDatabase(String name) throws SQLException{Connection connection = DriverManager.getConnection("jdbc:mysql://localhost", "root", "danding");     DatabaseMetaData tt = connection.getMetaData();          ResultSet rs = tt.getCatalogs();          while (rs.next()) {              if (rs.getString(1).equalsIgnoreCase(name)) {                  return true;              }          }          return false; }public boolean checkTables(String name) throws SQLException{Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "danding");DatabaseMetaData tt = connection.getMetaData();  ResultSet rs = tt.getTables("test", null, null, new String[]{"TABLE"});ResultSetMetaData metadata = rs.getMetaData();System.out.println(metadata.getColumnCount());while (rs.next()) {  if (rs.getString("TABLE_NAME").equalsIgnoreCase(name)) {  return true;  }  //for (int i = 1; i <= 10; i++) {//System.out.print(metadata.getColumnLabel(i)+" ");//}//System.out.println();}  return false; }public void run(){try {connect();System.out.println(checkTables("s"));} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void main(String[] args) {// TODO Auto-generated method stubnew Test().run();}}

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">注意 "create database ?" 不能用预处理，还有就是select ? from table;  等类似的不能用预处理。insert into table1 values(?,?,?)这样可以</span>

connection.setAutoCommit(false);   

PreparedStatement statement = connection.prepareStatement("INSERT INTO TABLEX VALUES(?, ?)");   

statement.setInt(1, 1); 
statement.setString(2, "Cujo"); 
statement.addBatch();   

statement.setInt(1, 2); 
statement.setString(2, "Fred"); 
statement.addBatch();   

statement.setInt(1, 3); 
statement.setString(2, "Mark"); 
statement.addBatch();   

int [] counts = statement.executeBatch();   

connection.commit();//如果没有这一步connection.setAutoCommit(false);  可以不提交

出现这个错误Can not issue data manipulation statements with executeQuery().

意味着，插入，更新，删除的时候，不应该用statement.executeQuery(),这只有查询结果集的时候才用。改为statement.execute(sql)