JDBC操作mysql编写及遇到的问题_MySQL

一。例子来自于一本书上：

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class MultiResultSet {	public static void main(String[] args) {		Connection conn = null;		Statement stmt = null;		// TODO Auto-generated method stub		try {			//装载JDBC驱动			Class.forName("com.mysql.jdbc.Driver");			//连接字符串：包括数据库服务器名、端口号（省略）、数据库名、			String conStr = "jdbc:mysql://localhost/mydb?characterEncoding=UTF8&allowMultiQueries=true";			//获得Connection对象，进行连接数据库：数据库连接字符串，用户名，密码			conn = DriverManager.getConnection(conStr, "root", "123456");			//获得Statement对象：用来操作sql语句			stmt = conn.createStatement();			//建立一个图书销售表t_booksale			String createDB = "create database if not exists mydb default character set utf8";			String dropTable = "drop table if exists mydb.t_booksale";//如果存在则删除重建			String createTable = "create table mydb.t_booksale(" +					"id int unsigned not null auto_increment,"+					"bookid int unsigned not null,"+					"amount int unsigned not null,"+					"saledate datetime not null,"+					"primary key(id)) engine=innodb default charset=utf8";			String insertData1 = "insert into mydb.t_booksale(bookid,amount,saledate) values(1,23,'1988-3-23')";			String insertData2 = "insert into mydb.t_booksale(bookid,amount,saledate) values(1,120,'2008-5-11')";						String insertData3 = "insert into mydb.t_booksale(bookid,amount,saledate) values(2,218,'2011-2-09')";					stmt.execute(createDB);			stmt.execute(dropTable);			stmt.execute(createTable);			stmt.execute(insertData1);			stmt.execute(insertData2);			stmt.execute(insertData3);			//返回多个结果集ResultSet，使用do while处理			String selectData = "select id,name,author from t_books" +";"+					"select bookid,amount,saledate from t_booksale";			if(stmt.execute(selectData)){				ResultSet rs = null;				do{					rs = stmt.getResultSet();					//显示查询结果					while(rs.next()){						System.out.println(rs.getString(1));//获得id号						System.out.println(rs.getString(2));//作者名						System.out.println(rs.getString(3));						}				}while(stmt.getMoreResults());			}					} catch (Exception e) {			// TODO Auto-generated catch block			e.printStackTrace();		}finally{			try {				if(stmt!=null){					stmt.close();					}				if(conn!=null){					conn.close();					}			} catch (SQLException e) {				// TODO Auto-generated catch block				e.printStackTrace();			}		}			}}

二。遇到的问题

1. 连接字符串问题。处理多个结果集时，要加上allowMultiQueries=true。

2. sql语句混合写时，要注意写时的格式问题。

3. 编码问题

4. Access denied for user 'root'@'localhost' (using password: YES)

   停止服务mysql----移除mysql服务-----安装mysql服务--------启动服务mysql