一、概念
JDBC:Java Database Connectivity
是什么:定义了操作所有关系型数据库(oracle、mysql)的规则(接口)。就是接口
数据库驱动:各个数据库厂商提供了JDBC的实现类jar包,通过jar包中类操作数据库。
二、步骤
1.下载驱动jar包,导入到项目中。
1)下载驱动
https://dev.mysql.com/downloads/
https://dev.mysql.com/downloads/connector/j/5.1.html
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/6d5d9384b99a8be1b31a7d0a790d5e59.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/6c62fa8bab94dc613a4de9e6a1c3be5b.png)
2)要导入的jar包名称【mysql-connector-java-5.1.49-bin.jar】
3)新建java项目project—module—package—class
4)在module上新建directory。
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/eb2cb597d33045920cde59137d65e060.png)
5)导入驱动jar包
(1)将jar包黏贴到步骤4)新建的文件夹中。
(2)在文件夹上右键【add as library】
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e922dd2c10ce136f3cc047c48c69b4a2.png)
2.注册驱动
3.获取数据库的连接对象Connection
4.定义sql(把sql发送给数据库操作数据库)
5.获取执行sql语句的对象 Statement
6.执行sql,接受返回结果
7.处理结果
8.释放资源
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbcDemo2 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.132.2:3306/mysql_test","mysql_test", "BJjbHSKFGESJrtpP");
String sql = "update book set author = 'ggg' where author = 'haha'";
Statement stat = conn.createStatement();
int count = stat.executeUpdate(sql);
System.out.println(count);
stat.close();
conn.close();
}
}
package jdbc;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
public class JdbcDemo1 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.132.2:3306/mysql_test", "mysql_test", "BJjbHSKFGESJrtpP");
String sql = "update book set author = 'haha' where author = 'yjj' ";
Statement statement = conn.createStatement();
int count = statement.executeUpdate(sql);
System.out.println(count);
statement.close();
conn.close();
}
}
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo4 {
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String sql = "update book set author = 'ggg' where author = 'haha'";
conn = DriverManager.getConnection("jdbc:mysql://192.168.132.2:3306/mysql_test", "mysql_test", "BJjbHSKFGESJrtpP");
stat = conn.createStatement();
int count = stat.executeUpdate(sql);
System.out.println(count);
if(count>=0){
System.out.println("执行成功");
}else{
System.out.println("执行失败");
}
}
catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3.添加异常处理,executeQuery,遍历获取数据 |
---|
package jdbc;
import java.sql.*;
public class JdbcDemo5 {
public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String sql = "select * from book";
conn = DriverManager.getConnection("jdbc:mysql://192.168.132.2:3306/mysql_test", "mysql_test", "BJjbHSKFGESJrtpP");
stat = conn.createStatement();
rs= stat.executeQuery(sql);
while(rs.next()){
String name = rs.getString(1);
String author = rs.getString(2);
System.out.println(name+","+author);
}
}
catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
package book;
public class Book {
private String name;
private String author;
public Book() {
}
public Book(String name, String author) {
this.name = name;
this.author = author;
}
@Override
public String toString() {
return "Book{" +
"name='" + name + '\'' +
", author='" + author + '\'' +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
}
package jdbc;
import book.Book;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcBook {
public static void main(String[] args) {
List<Book> all = new JdbcBook().findAll();
System.out.println(all);
System.out.println(all.size());
for (Book book : all) {
System.out.println(book);
}
}
public List<Book> findAll(){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
Book book = new Book();
List<Book> books = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://192.168.132.2:3306/mysql_test", "mysql_test", "BJjbHSKFGESJrtpP");
statement = connection.createStatement();
String sql = "select * from book";
resultSet = statement.executeQuery(sql);
books = new ArrayList<>();
while(resultSet.next()){
String name = resultSet.getString(1);
String author = resultSet.getString(2);
book.setName(name);
book.setAuthor(author);
books.add(book);
return books;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return books;
}
}
}
从数据库中查询所有学生信息,将学生信息封装为学生对象,将学生对象放到List集合中
package com.example.jdbc.test;
import com.example.jdbc.domain.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCTest01 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Student> studentList = new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/wkcto";
String user = "root";
String password = "admin123";
conn = DriverManager.getConnection(url,user,password);
String sql = "select id,name,birth from tbl_student";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
String id = rs.getString("id");
String name = rs.getString("name");
String birth = rs.getString("birth");
Student student = new Student();
student.setId(id);
student.setName(name);
student.setBirth(birth);
studentList.add(student);
}
}catch (Exception e){
e.printStackTrace();
}finally {
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
for (Student s :studentList){
System.out.println(s);
}
}
}