Maison >base de données >tutoriel mysql >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(); } } }}
二。遇到的问题
连接字符串问题。处理多个结果集时,要加上allowMultiQueries=true。
sql语句混合写时,要注意写时的格式问题。
编码问题
Access denied for user 'root'@'localhost' (using password: YES)
停止服务mysql----移除mysql服务-----安装mysql服务--------启动服务mysql