1.jdbc是什么
JDBC（Java DataBase Connectivity,java數(shù)據(jù)庫(kù)連接）是一種用于執(zhí)行SQL語(yǔ)句的Java API辐益，可以為多種關(guān)系數(shù)據(jù)庫(kù)提供統(tǒng)一訪(fǎng)問(wèn)，它由一組用Java語(yǔ)言編寫(xiě)的類(lèi)和接口組成硝枉。JDBC提供了一種基準(zhǔn)抹估，據(jù)此可以構(gòu)建更高級(jí)的工具和接口缠黍，使數(shù)據(jù)庫(kù)開(kāi)發(fā)人員能夠編寫(xiě)數(shù)據(jù)庫(kù)應(yīng)用程序。(百度百科)
jdbc經(jīng)常用來(lái)連接數(shù)據(jù)庫(kù)药蜻，創(chuàng)建sql或者mysql語(yǔ)句瓷式，使用相關(guān)的api去執(zhí)行sql語(yǔ)句，從而操作數(shù)據(jù)庫(kù)语泽，達(dá)到查看或者修改數(shù)據(jù)庫(kù)的目的贸典。學(xué)習(xí)jbbc要求對(duì)java編程有一定了解，并了解一種數(shù)據(jù)庫(kù)系統(tǒng)以及
sql語(yǔ)句踱卵。環(huán)境要求：
1.本地裝好jdk廊驼，并且裝好mysql數(shù)據(jù)庫(kù),我是直接裝過(guò)wamp帶有mysql數(shù)據(jù)庫(kù)/docker中安裝的mysql。
2.使用IDEA開(kāi)發(fā)
2.使用IDEA開(kāi)發(fā)
2.1 創(chuàng)建數(shù)據(jù)庫(kù)惋砂，數(shù)據(jù)表
我的mysql是使用docker創(chuàng)建的妒挎，如果是windows環(huán)境可以使用wamp較為方便。
數(shù)據(jù)庫(kù)名字是test,數(shù)據(jù)表的名字是student西饵，里面有四個(gè)字段酝掩，一個(gè)是id，也就是主鍵（自動(dòng)遞增）眷柔，還有名字期虾，年齡，成績(jī)驯嘱。最后先使用sql語(yǔ)句插入六個(gè)測(cè)試記錄镶苞。
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test;
CREATE TABLE `student` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(20) NOT NULL ,
`age` INT NOT NULL , `score` DOUBLE NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM;
INSERT INTO `student` VALUES (1, '小紅', 26, 83);
INSERT INTO `student` VALUES (2, '小白', 23, 93);
INSERT INTO `student` VALUES (3, '小明', 34, 45);
INSERT INTO `student` VALUES (4, '張三', 12, 78);
INSERT INTO `student` VALUES (5, '李四', 33, 96);
INSERT INTO `student` VALUES (6, '魏紅', 23, 46);
2.2 使用IDEA創(chuàng)建項(xiàng)目
我使用maven工程方式，項(xiàng)目目錄：
image
Student.class
package model;
/**
* student類(lèi)鞠评，字段包括id,name,age,score
* 實(shí)現(xiàn)無(wú)參構(gòu)造茂蚓，帶參構(gòu)造，toString方法，以及get煌贴，set方法
*/
public class Student {
private int id;
private String name;
private int age;
private double score;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(String name, int age, double score) {
super();
this.name = name;
this.age = age;
this.score = score;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age
+ ", score=" + score + "]";
}
}
DBUtil.class
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 工具類(lèi)御板，獲取數(shù)據(jù)庫(kù)的連接
* @author 秦懷
*
*/
public class DBUtil {
private static String URL="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&serverTimezone=UTC";
private static String USER="root";
private static String PASSWROD ="123456";
private static Connection connection=null;
static{
try {
Class.forName("com.mysql.jdbc.Driver");
// 獲取數(shù)據(jù)庫(kù)連接
connection=DriverManager.getConnection(URL,USER,PASSWROD);
System.out.println("連接成功");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 返回?cái)?shù)據(jù)庫(kù)連接
public static Connection getConnection(){
return connection;
}
}
StudentDao.class
package dao;
import model.Student;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import db.DBUtil;
/**
* 操作學(xué)生表的dao類(lèi)
* @author 秦懷
* 下面均使用預(yù)編譯的方法
*/
public class StudentDao {
//將連接定義為單例
private static Connection connection = DBUtil.getConnection();
// 添加新的學(xué)生
public void addStudent(Student student){
String sql ="insert into student(name,age,score) "+
"values(?,?,?)";
boolean result = false;
try {
// 將sql傳進(jìn)去預(yù)編譯
PreparedStatement preparedstatement = connection.prepareStatement(sql);
// 下面把參數(shù)傳進(jìn)去
preparedstatement.setString(1, student.getName());
preparedstatement.setInt(2, student.getAge());
preparedstatement.setDouble(3, student.getScore());
preparedstatement.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("創(chuàng)建數(shù)據(jù)庫(kù)連接失敗");
}
}
// 更新學(xué)生信息
public void updateStudent(Student student){
String sql = "update student set name = ? ,age =?,score = ? where id = ? ";
boolean result = false;
try {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, student.getName());
preparedStatement.setInt(2, student.getAge());
preparedStatement.setDouble(3, student.getScore());
preparedStatement.setInt(4, student.getId());
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("連接數(shù)據(jù)庫(kù)失敗");
}
}
// 根據(jù)id刪除一個(gè)學(xué)生
public void deleteStudent(int id){
String sql = "delete from student where id = ?";
boolean result = false;
try {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
result=preparedStatement.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 根據(jù)id查詢(xún)學(xué)生
public Student selectStudent(int id){
String sql ="select * from student where id =?";
try {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
Student student = new Student();
// 一條也只能使用resultset來(lái)接收
while(resultSet.next()){
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
student.setScore(resultSet.getDouble("score"));
}
return student;
} catch (SQLException e) {
// TODO: handle exception
}
return null;
}
// 查詢(xún)所有學(xué)生锥忿，返回List
public List<Student> selectStudentList(){
List<Student>students = new ArrayList<Student>();
String sql ="select * from student ";
try {
PreparedStatement preparedStatement = DBUtil.getConnection().prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
// 不能把student在循環(huán)外面創(chuàng)建牛郑，要不list里面六個(gè)對(duì)象都是一樣的，都是最后一個(gè)的值敬鬓，
// 因?yàn)閘ist add進(jìn)去的都是引用
// Student student = new Student();
while(resultSet.next()){
Student student = new Student();
student.setId(resultSet.getInt(1));
student.setName(resultSet.getString(2));
student.setAge(resultSet.getInt(3));
student.setScore(resultSet.getDouble(4));
students.add(student);
}
} catch (SQLException e) {
// TODO: handle exception
}
return students;
}
}
StudentAction.class
package action;
import java.util.List;
import dao.StudentDao;
import model.Student;
public class StudentAction {
/**
* @param args
*/
public static void main(String[] args) {
StudentDao studentDao = new StudentDao();
// TODO Auto-generated method stub
System.out.println("========================查詢(xún)所有學(xué)生========================");
List<Student> students =studentDao.selectStudentList();
for(int i=0;i<students.size();i++){
System.out.println(students.get(i).toString());
}
System.out.println("========================修改學(xué)生信息========================");
Student stu2 = new Student("Jam",20,98.4);
stu2.setId(2);
studentDao.updateStudent(stu2);
System.out.println("========================通過(guò)id查詢(xún)學(xué)生========================");
Student student = studentDao.selectStudent(2);
System.out.println(student.toString());
System.out.println("========================增加學(xué)生========================");
Student stu = new Student("new name",20,98.4);
studentDao.addStudent(stu);
System.out.println("========================刪除學(xué)生信息========================");
studentDao.deleteStudent(4);
System.out.println("========================查詢(xún)所有學(xué)生========================");
students =studentDao.selectStudentList();
for(int i=0;i<students.size();i++){
System.out.println(students.get(i).toString());
}
}
}
執(zhí)行的結(jié)果：
image
需要注意的點(diǎn)：
- 創(chuàng)建數(shù)據(jù)庫(kù)之后需要賦予用戶(hù)增刪改查的權(quán)限
- 如果不是使用maven方式導(dǎo)入包淹朋，需要將依賴(lài)的包復(fù)制進(jìn)來(lái)，并且add to path
- 以上代碼使用的是預(yù)編譯的方式钉答，這樣可以提高代碼的可讀性與維護(hù)性础芍，還有就是很大程度上防止了sql注入的問(wèn)題
- 如果不是用預(yù)編譯，那么就需要拼接sql語(yǔ)句数尿，很容易出錯(cuò)仑性，而且預(yù)編譯的作用是sql編譯過(guò)后，放在緩存中右蹦，這樣速度會(huì)更快诊杆。
- 使用拼接方式參考下面這段代碼：
sql = "select * from table where name= '" + name + "' and password= '" + password+"'";
Statement statement = connection.createStatement();
ResultSet resultset = statement.executeQuery(sql);
pom文件使用到的依賴(lài),必須和自己的數(shù)據(jù)庫(kù)版本匹配，要不會(huì)連接失敗
<dependencies>
<!-- mysql驅(qū)動(dòng)包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
</dependencies>
【作者簡(jiǎn)介】：
秦懷何陆，公眾號(hào)【秦懷雜貨店】作者晨汹，技術(shù)之路不在一時(shí)，山高水長(zhǎng)贷盲，縱使緩慢淘这，馳而不息。這個(gè)世界希望一切都很快巩剖，更快铝穷，但是我希望自己能走好每一步，寫(xiě)好每一篇文章佳魔，期待和你們一起交流曙聂。
此文章僅代表自己（本菜鳥(niǎo)）學(xué)習(xí)積累記錄，或者學(xué)習(xí)筆記吃引，如有侵權(quán)筹陵，請(qǐng)聯(lián)系作者核實(shí)刪除。人無(wú)完人镊尺，文章也一樣朦佩，文筆稚嫩，在下不才庐氮，勿噴语稠，如果有錯(cuò)誤之處，還望指出，感激不盡~
