MySQL驱动包
Java程序要连接MySQL数据库,需要使用MySQL驱动包(jar包),当前驱动包的最新版本是:mysql-connector-java-8.0.21.zip
。解压这个压缩包,会看到如下的文件夹结构,其中的mysql-connector-java-8.0.21.jar
就是我们需要的MySQL驱动包。把这个jar包复制到Java项目的用于存储库文件的libs文件夹
中,再在IDE里导入这个jar包,如此就可以使用jar包里的API了。
代码示例
mysql-connector在8.0之前和之后的jar包是不同的,在使用时会一定的区别。在进行如下操作前:需保证已安装了MySQL数据库,并打开了MySQL数据服务器(启动服务器:“net start mysql”,停止服务器:“net stop mysql”),其次,还需保证创建了数据库(可通过附录中的mysql.sql数据库脚本来创建数据库,并插入相应的数据)。
1、连接数据库需要四个信息:驱动名、数据库url、用户名和密码,一般被声明为静态常量。
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/auction"; static final String USER = "root"; static final String PASS = "123456";
2、对数据库操作一般分为如下的三个步骤:
a、注册驱动和连接数据库
Class.forName(JDBC_DRIVER);System.out.println("连接数据库...");Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
b、进行增删改查等数据操作
System.out.println(" 实例化Statement对象...");Statement stmt = conn.createStatement();String sql;sql = "SELECT user_id, username, userpass, email FROM auction_user";ResultSet rs = stmt.executeQuery(sql);while(rs.next()){int user_id = rs.getInt("user_id");String username = rs.getString("username");String userpass = rs.getString("userpass");String email = rs.getString("email");System.out.print("用户ID: " + user_id);System.out.print(", 用户名称: " + username);System.out.print(", 用户密码: " + userpass);System.out.print(", 邮箱: " + email);System.out.print("\n");}
c、按顺序关闭资源
rs.close(); stmt.close(); conn.close();
附录
mysql.sql脚本的使用方式:(1)登录进MySQL数据库(在cmd中输入命令“mysql -u username -p”,按回车,待提示输入密码时,再输入用户密码
),(2)执行mysql脚本(source ***/mysql.sql
)。
脚本文件:mysql.sql
drop database if exists auction;create database auction;use auction;
create table auction_user(user_id int(11) auto_increment,username varchar(50) not null,userpass varchar(50) not null,email varchar(100) not null,primary key(user_id),unique(username)
);INSERT INTO auction_user (username,userpass,email) VALUES ('tomcat','tomcat','spring_test@163.com');
INSERT INTO auction_user (username,userpass,email) VALUES ('mysql','mysql','spring_test@163.com');
create table kind(kind_id int(11) auto_increment,kind_name varchar(50) not null, kind_desc varchar(255) not null,primary key(kind_id)
);INSERT INTO kind (kind_name,kind_desc) VALUES ('电脑硬件','这里并不是很主流的产品,但价格绝对令你心动');
INSERT INTO kind (kind_name,kind_desc) VALUES ('房产','提供非常稀缺的房源');
create table state(state_id int(11) auto_increment,state_name varchar(10),primary key(state_id)
);INSERT INTO state (state_name) VALUES ('拍卖中');
INSERT INTO state (state_name) VALUES ('拍卖成功');
INSERT INTO state (state_name) VALUES ('流拍');
create table item(item_id int(11) auto_increment,item_name varchar(255) not null,item_remark varchar(255),item_desc varchar(255),kind_id int(11) not null, addtime date not null,endtime date not null,init_price double not null, max_price double not null, owner_id int(11) not null, winer_id int(11), state_id int(11) not null, primary key(item_id),FOREIGN KEY(kind_id) REFERENCES kind(kind_id), FOREIGN KEY(owner_id) REFERENCES auction_user(user_id),FOREIGN KEY(winer_id) REFERENCES auction_user(user_id),FOREIGN KEY(state_id) REFERENCES state(state_id)
);
INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price, max_price, owner_id, winer_id, state_id)VALUES ( '主板', '老式主板', '老主板,还可以用', 1, ADDDATE(CURDATE(), -5), ADDDATE(CURDATE(), 30) , 230, 250, 1, null, 1);
INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price, max_price, owner_id, winer_id, state_id)VALUES ( '显卡', '老式显卡', '老显卡,还可以用', 1, ADDDATE(CURDATE(), -9), ADDDATE(CURDATE(), -2), 210, 210, 2, null, 3);
INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price, max_price, owner_id, winer_id, state_id)VALUES ( '老房子', '老式房子', '40年的老房子', 2, ADDDATE(CURDATE(), -9), ADDDATE(CURDATE(), -5), 21000, 25000, 2, 1, 2);
create table bid(bid_id int(11) auto_increment,user_id int(11) not null,item_id int(11) not null,bid_price double not null,bid_date date not null, primary key(bid_id),unique(item_id , bid_price),FOREIGN KEY(user_id) REFERENCES auction_user(user_id), FOREIGN KEY(item_id) REFERENCES item(item_id)
);INSERT INTO bid ( user_id , item_id , bid_price, bid_date)VALUES ( 2, 1, 250, ADDDATE(CURDATE(), -2));
INSERT INTO bid ( user_id , item_id , bid_price, bid_date)VALUES ( 1, 3, 25000, ADDDATE(CURDATE(), -6));