JDBC复习

什么是JDBC

java连接数据库！

需要jar包的支持

java-sql

javax-sql

mysql-conneter-java... 连接驱动（必须要导入）

实验环境搭建

表名

CREATE TABLE users(

插入数据

id INT PRIMARY KEY,

`name` VARCHAR(40),

`password` VARCHAR(40),

email VARCHAR(60),

birthday DATE

);

插入数据

INSERT INTO users(id,`name`,`password`,email,birthday)

VALUES(1,'张三','123456','zs@qq.com','2000-01-01');

INSERT INTO users(id,`name`,`password`,email,birthday)

VALUES(2,'李四','123456','ls@qq.com','2000-01-01');

INSERT INTO users(id,`name`,`password`,email,birthday)

VALUES(3,'王五','123456','ww@qq.com','2000-01-01');

//查询所有表

SELECT * FROM users;

导入数据库依赖

<dependencies>

<dependency>

<!-- mysql驱动-->

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>5.1.47</version>

</dependency>

</dependencies>

IDEA中连接数据库

JDBC固定步骤

1.加载驱动

2.连接数据库,代表数据库

3.向数据库发送SQL对象 Statement：CRUD

4.编写SQL（根据业务，不同的SQL）

5.执行SQL

6.关闭连接

package com.bubbles.tsst;

import java.sql.*;

public class TestJdbc {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

//配置信息

//useUnicode=true&characterEncoding=utf-8 解决中文乱码

String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";

String username = "root"; //用户

String password = "Root";//密码

//1.加载驱动

Class.forName ("com.mysql.jdbc.Driver");

//2.获取数据库连接,代表数据库

Connection connection = DriverManager.getConnection (url, username,password);

//3.向数据库发送SQL对象 Statement, PreparedStatement：CRUD

Statement statement = connection.createStatement ();

//4.编写SQL

String sql = "select * from users";

//5.执行查询SQL，返回一个ResultSet ：结果集

ResultSet rs = statement.executeQuery (sql);

while (rs.next ()) {

System.out.println ("id="+rs.getObject ("id"));

System.out.println ("name="+rs.getObject ("name"));

System.out.println ("password="+rs.getObject ("password"));

System.out.println ("email="+rs.getObject ("email"));

System.out.println ("birthday="+rs.getObject ("birthday"));

}

//6.关闭连接，释放资源（一定要做）先开后关

rs.close ();

statement.close ();

connection.close ();

}

}

预编译SQL

package com.bubbles.tsst;

import java.sql.*;

public class TestJdbc2 {

public static void main(String[] args) throws Exception {

//配置信息

//useUnicode=true&characterEncoding=utf-8 解决中文乱码

String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";

String username = "root"; //用户

String password = "Root";//密码

//1.加载驱动

Class.forName ("com.mysql.jdbc.Driver");

//2.获取数据库连接,代表数据库

Connection connection = DriverManager.getConnection (url, username,password);

//3.编写SQL

String sql = "insert into users(id, name, password, email, birthday) values (?,?,?,?,?);";

//4.预编译

PreparedStatement preparedStatement = connection.prepareStatement (sql);

preparedStatement.setInt (1,5);//给第一个占位符？ 的值赋值为1;

preparedStatement.setString (2,"bubbles");//给第二个占位符？ 的值赋值为Bubbles;

preparedStatement.setString (3,"123456");//给第三个占位符？ 的值赋值为123456;

preparedStatement.setString (4,"24736743@qq.com");//给第四个占位符？ 的值赋值为2019882328@qq.com;

preparedStatement.setDate (5,new Date (new java.util.Date().getTime ()));//给第五个占位符？ 的值赋值为new java.util.Date().getTime ());

//5.执行SQL

int i = preparedStatement.executeUpdate ();

if(i>0){

System.out.println ("插入成功@ ");

}

//6.关闭连接，释放资源（一定要做）先开后关

preparedStatement.close ();

connection.close ();

}

}