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 block
			e.printStackTrace();
		}
		
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		new 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)