欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-13 16:51:11
...
一。例子来自于一本书上:
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();			}		}			}}

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

二。遇到的问题

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

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

  3. 编码问题

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

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