通过jdbc连接mysql
1.添加maven依赖
仓库地址:https://mvnrepository.com/artifact/mysql/mysql-connector-java
maven依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
2.数据库文件:
2.1.创建数据库:
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4;
2.2.创建表:
CREATE TABLE IF NOT EXISTS `class`(
`id` int(10) not null AUTO_INCREMENT comment '主键',
`name` varchar(50) not null default '' comment '名称',
`del_flag` tinyint(4) not null default '0' comment '删除标识 0为未删除 1为已删除',
PRIMARY KEY(`id`)
)ENGINE=InnoDB default charset=utf8 comment='班级';
2.3.新建数据:
INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级a', 0);
INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级b', 0);
INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级c', 0);
INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级d', 0);
INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级e', 0);
INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级f', 0);
3.代码分析
3.1 Class.forName("com.mysql.jdbc.Driver");
加载JDBC驱动程序;
3.2 DriverManager.getConnection(url , username , password );
创建数据库的连接
其中参数url后面带的参数:useSSL=false&useUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true具体作用,请自行了解
3.3 PreparedStatement pstmt = con.prepareStatement(sql);
创建一个preparedStatement
注:要执行SQL语句,必须获得java.sql.Statement实例,Statement实例分为以下3 种类型:
1、执行静态SQL语句。通常通过Statement实例实现,Statement stmt = con.createStatement() ;
2、执行动态SQL语句。通常通过PreparedStatement实例实现。 PreparedStatement pstmt = con.prepareStatement(sql) ;
3、执行数据库存储过程。通常通过CallableStatement实例实现。CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;
3.4 pstmt.executeQuery(),pstmt.executeUpdate()
执行SQL语句
注:Statement接口提供了三种执行SQL语句的方法:executeQuery 、executeUpdate和execute
1、ResultSet executeQuery(String sqlString):执行查询数据库的SQL语句,返回一个结果集(ResultSet)对象。
2、int executeUpdate(String sqlString):用于执行INSERT、UPDATE或 DELETE语句以及SQL DDL语句,如:CREATE TABLE和DROP TABLE等
3、execute(sqlString):用于执行返回多个结果集、多个更新计数或二者组合的语句
4.代码:
/**
* 初始化数据库连接
*/
public static Connection init() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver") ;
String url = "jdbc:mysql://127.0.0.1:3306/test_db?useSSL=false&useUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true" ;
String username = "root" ;
String password = "12345678" ;
return DriverManager.getConnection(url , username , password );
}
* 关闭数据库对象
*/
public static void closeObject(Connection con,PreparedStatement pstmt,ResultSet rs) {
if(pstmt != null){ // 关闭声明
try{
pstmt.close() ;
}catch(SQLException e){
e.printStackTrace() ;
}
}
if(con != null){ // 关闭连接对象
try{
con.close() ;
}catch(SQLException e){
e.printStackTrace() ;
}
}
if (rs != null) {
try{
rs.close() ;
}catch(SQLException e){
e.printStackTrace() ;
}
}
}
/**
* 查询
*/
public static void select() {
try {
Connection con = init();
String sql = "select * from class";
PreparedStatement pstmt = con.prepareStatement(sql) ;
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1) + "----" + rs.getString(2) + "----" + rs.getInt(3));
}
closeObject(con,pstmt,rs);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
/**
* 新增
*/
public static void insert() {
try {
Connection con = init();
String sql = "INSERT INTO `class`( `name`, `del_flag`) VALUES ('班级h', 0);";
PreparedStatement pstmt = con.prepareStatement(sql) ;
int count = pstmt.executeUpdate();
if (count >= 1) {
System.out.println("新增成功");
select();
}
closeObject(con,pstmt,null);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 更新
*/
public static void update() {
try {
Connection con = init();
String sql = "UPDATE class set name = '修改班级名字' where id = 1;";
PreparedStatement pstmt = con.prepareStatement(sql) ;
int count = pstmt.executeUpdate();
if (count >= 1) {
System.out.println("修改成功");
select();
}
closeObject(con,pstmt,null);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 删除
*/
public static void delete() {
try {
Connection con = init();
String sql = "delete from class where id = 1;";
PreparedStatement pstmt = con.prepareStatement(sql) ;
int count = pstmt.executeUpdate();
if (count >= 1) {
System.out.println("删除成功");
select();
}
closeObject(con,pstmt,null);
} catch (Exception e) {
e.printStackTrace();
}
}