数据库入门学习，Java连接MySQL数据库
程序员文章站
2022-04-04 23:24:01
...
Java连接MySQL数据库
最近为了完成学校的课业，制作了一个简单的通过Java来操作数据库的demo
第一步，创建项目
首先我用的编辑器是IDEA
- 新建spring boot项目
file->new->project,然后选择spring assistant.
第二步，和数据库建立连接
合理的创建标题，有助于目录的生成
package com.example.demo.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DAOsupport {
static Connection connection;
static String driver="com.mysql.jdbc.Driver";
static String url="jdbc:mysql://127.0.0.1:3306/student?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
static String username="root";
static String password="zaqw2a701";
public DAOsupport(){
try{
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("加载数据库出错");
e.printStackTrace();
}
try {
connection= DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
System.out.println("连接数据库出错");
e.printStackTrace();
}
}
}
第三步，创建一个类来保存数据表中的内容
package com.example.demo.domain;
public class Exam {
String studentno;
String majorid;
String score;
public Exam(String stuno,String majid,String score){
this.studentno=stuno;
this.majorid=majid;
this.score=score;
}
public Exam(){}
public String getStudentno() {
return studentno;
}
public String getMajorid() {
return majorid;
}
public String getScore() {
return score;
}
public void setStudentno(String studentno) {
this.studentno = studentno;
}
public void setMajorid(String majorid) {
this.majorid = majorid;
}
public void setScore(String score) {
this.score = score;
}
}
接着实现以下操作：创建表，创建存储过程，函数，事务，视图，并在Java中调用
1.创建表
public void createDB() {
String sql = "CREATE TABLE exam(" +
"studentno VARCHAR(10)," +
"majorid VARCHAR(10)," +
"score INT," +
"primary key(studentno,majorid)" + ")";
Statement ptatement;
try {
ptatement = connection.createStatement();
if (0 == ptatement.executeUpdate(sql)) {
System.out.println("创建表成功");
} else {
System.out.println("创建表失败");
}
ptatement.close();
connection.close();
} catch (SQLException e) {
System.out.println("创建表失败");
e.printStackTrace();
}
}
2.1 在数据库创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `exam_procedure`(in studentno varchar(10),in majorid varchar(10),in score int)
BEGIN
insert into exam (exam.studentno,exam.majorid,exam.score)
values(studentno,majorid,score);
END
2.2 在Java中调用
public void insertDB(String stuno, String majorid, int score) {
CallableStatement stmt = null;
try {
String sql = "call exam_procedure(?,?,?)";
stmt = connection.prepareCall(sql);
stmt.setString(1, stuno);
stmt.setString(2, majorid);
stmt.setInt(3, score);
stmt.execute();
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
3.1 在数据库中创建函数，这里函数的功能是删除对应学生的考试成绩
CREATE DEFINER=`root`@`localhost` FUNCTION `exam`(sno VARCHAR(20),mid INT) RETURNS int(11)
BEGIN
DECLARE d1 INT;
DECLARE d2 INT;
SET d1 = (SELECT COUNT(*) FROM exam WHERE studentno = sno AND majorid = mid);
DELETE FROM exam WHERE studentno = sno AND majorid = mid;
SET d2 = (SELECT COUNT(*) FROM exam WHERE studentno = sno AND majorid = mid);
RETURN d1+d2;
END
3.2 同样在数据库中调用
public static void deleteDB(String studentno,String majorid){
try {
int result = 0;
CallableStatement callStmt = connection.prepareCall("SELECT exam(?,?)");
callStmt.setString(1,studentno);
callStmt.setString(2,majorid);
callStmt.execute();
ResultSet rs = callStmt.getResultSet();
if (rs.next()) {
result = rs.getInt(1);
}
System.out.println(result);
callStmt.close();
connection.close();
} catch (SQLException e) {
System.out.println(e);
}
}
- 在Java中创建事务，来修改学生每门课的成绩
public static void updateDB(String studentno,int score1,int score2,int score3){
String sql;
PreparedStatement pst;
int score[] = {score1,score2,score3};
try {
connection.setAutoCommit(false);
for (int i = 0;i < 3;i++){
sql = "UPDATE exam SET score = ? WHERE studentno = ? AND majorid = ?";
pst = connection.prepareStatement(sql);
pst.setInt(1,score[i]);
pst.setString(2,studentno);
pst.setInt(3,i+1);
pst.executeUpdate();
}
connection.commit();
connection.setAutoCommit(true);
System.out.println("修改成功");
connection.close();
} catch (SQLException e) {
e.printStackTrace();
//一旦事务中有哪一步操作发生异常则进行事务回滚
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
5.1 在数据库中创建视图，显示学生的信息
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `newexam` AS
SELECT
1 AS `学号`,
1 AS `姓名`,
1 AS `性别`,
1 AS `课程名字`,
1 AS `课程分数`
5.2 同样在Java中去调用它
public void select(){
String studentno;//学号
String studentname;//姓名
String sex;//性别
String majorname;//课程名字
Double score;//课程分数
try {
Statement statement = connection.createStatement();
String sql = "SELECT * FROM newexam;";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("学号"+"\t"+"姓名"+"\t"+"性别"+"\t"+"课程名字"+"\t\t"+"课程分数");
while (resultSet.next()) {
studentno = resultSet.getString("学号");
studentname = resultSet.getString("姓名");
sex = resultSet.getString("性别");
majorname = resultSet.getString("课程名字");
score = resultSet.getDouble("课程分数");
System.out.println(studentno+"\t"+studentname+"\t"+sex+"\t\t"+majorname+"\t\t"+score);
}
resultSet.close();
connection.close();
} catch (SQLException e) {
System.out.println(e);
}
}
- 最后别忘了在主函数中实现上面的操作
public static void main(String[] args) throws SQLException {
SpringApplication.run(DemoApplication.class, args);
Exam exam=new Exam();
ExamDao examDao=new ExamDao(exam);
examDao.createDB();//创建exam表
examDao.queryDB();//存储过程
examDao.deleteDB("S015","3");//删除函数
examDao.updateDB("S003",80,50,60);//事务
examDao.select();//显示视图
}
总结：这只是一个非常基础的数据库操作，后期还能加入各种各样的查询操作，制作一个前端的UI界面实现数据传递，就可以让这个程序越来越有趣了！
这篇文章比较适合刚学习数据库的同学，我也是刚刚入门。。。
希望大佬看到能睁只眼闭只眼哈哈o(￣▽￣)ブ