作为半路入门的程序猿,从来没有系统地做过一个web项目,最近想尝试一下,了解一下从后台到前端的整个过程。准备尝试的第一步就是Java如何连接MySQL数据库,网上相关博客很多,在这里重新写一下是为了加深自己的印象,也方便日后查询。
1. 数据库准备
打开MySQL workbench,新建一个数据库,命名为sqltestdb,在数据库中建立一个表department。
create database sqltestdb;
use sqltestdb;
create table department(
dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key(dept_name)
);
insert into department values('Biology','Watson','90000');
insert into department values('Comp.Sci.','Taylor','100000');
insert into department values('Elec.Eng.','Taylor','85000');
insert into department values('Finance','Painter','120000');
insert into department values('History','Painter','50000');
insert into department values('Music','Packard','80000');
insert into department values('Physics','Watson','70000');
2. 新建Java工程
创建一个Java工程,命名为Connect_to_MySQL。下载MySQL连接驱动,实际就是一个jar包mysql-connector-java-8.0.15.jar。右键项目选择build path将jar包导入到项目中。
3. 代码
package sqldemo;
import java.sql.*;
public class Main {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
//驱动程序名
String driver = "com.mysql.jdbc.Driver";
try {
//加载驱动程序
Class.forName(driver);
System.out.println("\n驱动程序已装载");
System.out.println("即将连接数据库");
}catch(Exception e) {
System.out.println("无法加载驱动程序");
}
//声明connection对象
Connection con;
//URL指向要访问的数据库名
String url = "jdbc:mysql://localhost:3306/sqltestdb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
//MySQL配置时的用户名和密码
String user = "root";
String password = "******";
//遍历查询结果集
try {
//.getConnection方法连接MySQL数据库
con=DriverManager.getConnection(url,user,password);
if(!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
//创建statement类对象来执行SQL语句
Statement statement=con.createStatement();
//要执行的SQL语句
String sql="select * from department";
//ResultSet类用来存放获取的结果集
ResultSet rs = statement.executeQuery(sql);
System.out.println("------------");
System.out.println("执行结果如下所示:");
System.out.println("------------");
System.out.println("系名"+"\t"+"薪酬");
System.out.println("------------");
String name=null;
String salary=null;
while(rs.next()) {
//获取stuname这列数据
name=rs.getString("dept_name");
salary=rs.getString("budget");
System.out.println(name+"\t"+salary);
}
rs.close();
con.close();
}catch(SQLException e) {
System.out.println("SQLException\n");
e.printStackTrace();
}catch(Exception e) {
System.out.println("Exception\n");
e.printStackTrace();
}finally {
System.out.println("数据库数据获取成功");
}
}
}
4. 结果展示