共计 7265 个字符,预计需要花费 19 分钟才能阅读完成。
[TOC]
1.jdbc 是什么
JDBC
(Java DataBase Connectivity,java 数据库连贯)是一种用于执行 SQL 语句的Java API
,能够为多种关系数据库提供对立拜访,它由一组用 Java 语言编写的类和接口组成。JDBC
提供了一种基准,据此能够构建更高级的工具和接口,使数据库开发人员可能编写数据库应用程序。(百度百科)jdbc
常常用来连贯数据库,创立sql
或者mysql
语句,应用相干的api
去执行 sql 语句,从而操作数据库,达到查看或者批改数据库的目标。- 学习 jbbc 要求对 java 编程有肯定理解,并理解一种数据库系统以及
sql
语句。- 环境要求:
1. 本地装好
jdk
,并且装好mysql
数据库, 我是间接装过wamp
带有mysql
数据库 /docker
中装置的 mysql。
2. 应用 IDEA 开发
2. 应用 IDEA 开发
2.1 创立数据库,数据表
我的 mysql 是应用 docker 创立的,如果是 windows 环境能够应用 wamp 较为不便。
数据库名字是 test, 数据表的名字是 student,外面有四个字段,一个是 id,也就是主键(主动递增),还有名字,年龄,问题。最初先应用 sql 语句插入六个测试记录。
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 创立我的项目
我应用 maven 工程形式,我的项目目录:
Student.class
package model;
/**
* student 类,字段包含 id,name,age,score
* 实现无参结构,带参结构,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;
/**
* 工具类,获取数据库的连贯
* @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");
// 获取数据库连贯
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();}
}
// 返回数据库连贯
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;
/**
* 操作学生表的 dao 类
* @author 秦怀
* 上面均应用预编译的办法
*/
public class StudentDao {
// 将连贯定义为单例
private static Connection connection = DBUtil.getConnection();
// 增加新的学生
public void addStudent(Student student){String sql ="insert into student(name,age,score)"+
"values(?,?,?)";
boolean result = false;
try {
// 将 sql 传进去预编译
PreparedStatement preparedstatement = connection.prepareStatement(sql);
// 上面把参数传进去
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("创立数据库连贯失败");
}
}
// 更新学生信息
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("连贯数据库失败");
}
}
// 依据 id 删除一个学生
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();}
}
// 依据 id 查问学生
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 来接管
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;
}
// 查问所有学生,返回 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 在循环里面创立,要不 list 外面六个对象都是一样的,都是最初一个的值,// 因为 list add 进去的都是援用
// 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("======================== 查问所有学生 ========================");
List<Student> students =studentDao.selectStudentList();
for(int i=0;i<students.size();i++){System.out.println(students.get(i).toString());
}
System.out.println("======================== 批改学生信息 ========================");
Student stu2 = new Student("Jam",20,98.4);
stu2.setId(2);
studentDao.updateStudent(stu2);
System.out.println("======================== 通过 id 查问学生 ========================");
Student student = studentDao.selectStudent(2);
System.out.println(student.toString());
System.out.println("======================== 减少学生 ========================");
Student stu = new Student("new name",20,98.4);
studentDao.addStudent(stu);
System.out.println("======================== 删除学生信息 ========================");
studentDao.deleteStudent(4);
System.out.println("======================== 查问所有学生 ========================");
students =studentDao.selectStudentList();
for(int i=0;i<students.size();i++){System.out.println(students.get(i).toString());
}
}
}
执行的后果:
须要留神的点:
- 创立数据库之后须要赋予用户增删改查的权限
- 如果不是应用 maven 形式导入包,须要将依赖的包复制进来,并且 add to path
- 以上代码应用的是预编译的形式,这样能够进步代码的可读性与维护性,还有就是很大水平上避免了 sql 注入的问题
- 如果不是用预编译,那么就须要拼接 sql 语句,很容易出错,而且预编译的作用是 sql 编译过后,放在缓存中,这样速度会更快。
- 应用拼接形式参考上面这段代码:
sql = "select * from table where name='" + name + "'and password='" + password+"'";
Statement statement = connection.createStatement();
ResultSet resultset = statement.executeQuery(sql);
pom 文件应用到的依赖,必须和本人的数据库版本匹配,要不会连贯失败
<dependencies>
<!-- mysql 驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
</dependencies>
此文章仅代表本人(本菜鸟)学习积攒记录,或者学习笔记,如有侵权,请分割作者删除。人无完人,文章也一样,文笔稚嫩,在下不才,勿喷,如果有谬误之处,还望指出,感激不尽~
技术之路不在一时,山高水长,纵使迟缓,驰而不息。
公众号:秦怀杂货店
正文完