文章目录
一、实验要求
要求:
- 配置好maven;
- 创建一个maven项目,将mysql-connector-java-8.0.13.jar配置到pom.xml文件 中;
- 参考TestDao.java程序,自行设计一个程序,访问myschool数据库的course表;
- 将运行结果截图,和程序一起提交。
二、具体流程
1、安装和配置Maven
首先要先安装和配置好maven和maven仓库之类的,根据Maven_实验指导.pdf里面步骤一步步安装和配置就行
2、创建一个maven项目
File—New—Project新建一个项目:
选中maven:
next后在Name和Location处配置项目名字和位置(Artifact Coordinates可以直接用默认的)然后点finish就好了:
3、配置pom.xml文件
我们可以看到正常新建完maven项目后大致是这样的
打开pom.xml文件开始配置
这里要注意配置的mysql驱动版本跟自己的mysql版本最好一致,要求里的是8.0.13版本,但我之前用的是8.0.17的mysql所以我驱动也用的8.0.17
关于mysql版本的查看:
cmd中输入mysql --version
:
关于maven依赖的相关信息查询:
这是maven官方的仓库查询地址:https://mvnrepository.com/
在输入框里输入想要查找的依赖点击search:
找到所需要的依赖:
在里面找到所需要的版本点击进入(我的是8.0.17):
就能找到maven中该依赖的写法啦
也可以直接复制这个↓,不过要根据自己的版本改一下版本号
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<!-- 我本地的mysql版本装的是8.0.17,所以导入对应版本的驱动-->
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
要注意官网中复制的话是<dependency> </dependency>
标签里面的,但在pom.xml里面<dependency> </dependency>
标签外面还有一层<dependencies> </dependencies>
!!!
最终的pom.xml文件长这样:
别忘了要点一下右上角的这个来更新一下:
pom.xml配置好后可以在右边这个栏里看看依赖有没有成功导入:
然后就可以开始写代码了!
4、项目结构
5、参考代码
关于database.properties配置文件先说一下
- mysql8中driver是
com.mysql.cj.jdbc.Driver
- mysql8中的url建议写成:
jdbc:mysql://服务器ip:端口/数据库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
比如这个项目里面在本地服务器3306端口的话就是jdbc:mysql://localhost:3306/myschool?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
用老师的那个的话可能会报错- 密码是自己的数据库登录的时候的那个密码
- com.java.DB.DBUtilConfig中这一句注意路径就直接是
database.properties
看的是编译后的路径,也就是target文件夹中的路径
具体原因可以参考:Maven获取resources的文件路径、读取resources的文件
com.java.Dao包
com.java.Dao.CourseDao
package com.java.Dao;
import com.java.Entity.Course;
import java.util.List;
public interface CourseDao {
public boolean add(Course item);
public boolean deleteById(int id);
public boolean update(Course item);
public List<Course> findAll();
public List<Course> findById(int id);
}
com.java.Dao.UserDao
package com.java.Dao;
import com.java.Entity.User;
import java.util.List;
//专门处理user表的接口
public interface UserDao {
public boolean add(User item);
public boolean deleteById(int id);
public boolean update(User item);
public List<User> findAll();
public List<User> findById(int id);
}
com.java.Dao.Impl包
com.java.Dao.Impl.CourseDaoImpl
package com.java.Dao.Impl;
import com.java.DB.DBUtilConfig;
import com.java.Dao.CourseDao;
import com.java.Entity.Course;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @projectName:
* @package: com.java.Dao.Impl
* @className: CourseDaoImpl
* @author: GCT
* @description: TODO
* @date: 2022/9/26 18:45
* @version: 1.0
*/
public class CourseDaoImpl implements CourseDao {
private DBUtilConfig db;
public CourseDaoImpl() {
db=new DBUtilConfig();
}
@Override
public boolean add(Course item) {
// String sql="insert into course(cname,credit,department,content) values('"+item.getCname()+"','"+item.getCredit()+"','"+item.getDepartment()+"','"+item.getContent()+"')";
String sql="insert into course(id,cname,credit,department,content) values(null,?,?,?,?)";
Object[] params = { item.getCname(),item.getCredit(),item.getDepartment(),item.getContent() };
return db.execUpdate(sql,params);
}
@Override
public boolean deleteById(int id) {
String sql="delete from course where id = ?";
Object[] params = { id };
return db.execUpdate(sql,params);
}
@Override
public boolean update(Course item) {
String sql="update course set cname= ?,credit = ?,department = ?,content = ? where id = ?";
Object[] params = { item.getCname(),item.getCredit(),item.getDepartment(),item.getContent(),item.getId() };
return db.execUpdate(sql,params);
}
@Override
public List<Course> findById(int id) {
List<Course> courseList = new ArrayList<Course>();
String selectCourse="select * from course where id = ?";
// 封装参数
Object[] selparams = { id };
//2.执行并接收查询结果
ResultSet rest= db.execQueryById(selectCourse,selparams);
try {
while(rest.next())
{
int iid=rest.getInt("id");
String cname=rest.getString("cname");
int credit=rest.getInt("credit");
String department=rest.getString("department");
String content=rest.getString("content");
courseList.add(new Course(iid,cname,credit,department,content));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
db.DBclose();
}
return courseList;
}
@Override
public List<Course> findAll() {
List<Course> courseList = new ArrayList<Course>();
String selectCourse="select * from course";
// 封装参数
// Object[] selparams = { 1 };
//2.执行并接收查询结果
ResultSet rest= db.execQueryAll(selectCourse);
try {
while(rest.next())
{
int id=rest.getInt("id");
String cname=rest.getString("cname");
int credit=rest.getInt("credit");
String department=rest.getString("department");
String content=rest.getString("content");
courseList.add(new Course(id,cname,credit,department,content));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
db.DBclose();
}
return courseList;
}
}
com.java.Dao.Impl.UserDaoImpl
package com.java.Dao.Impl;
import com.java.DB.DBUtilConfig;
import com.java.Dao.UserDao;
import com.java.Entity.User;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//UserDao的实现类,通过DBUtil访问数据库
public class UserDaoImpl implements UserDao {
private DBUtilConfig db;
public UserDaoImpl() {
db=new DBUtilConfig();
}
@Override
public boolean add(User item) {
// String sql="insert into course(cname,credit,department,content) values('"+item.getCname()+"','"+item.getCredit()+"','"+item.getDepartment()+"','"+item.getContent()+"')";
String sql="insert into users(id,username,password) values(null,?,?)";
Object[] params = { item.getUsername(),item.getPassword() };
return db.execUpdate(sql,params);
}
@Override
public boolean deleteById(int id) {
String sql="delete from users where id = ?";
Object[] params = { id };
return db.execUpdate(sql,params);
}
@Override
public boolean update(User item) {
String sql="update users set username= ?,password = ? where id = ?";
Object[] params = { item.getUsername(),item.getPassword(),item.getId() };
return db.execUpdate(sql,params);
}
@Override
public List<User> findById(int id) {
List<User> usersList = new ArrayList<User>();
String selectUsers="select * from users where id = ?";
// 封装参数
Object[] selparams = { id };
//2.执行并接收查询结果
ResultSet rest= db.execQueryById(selectUsers,selparams);
try {
while(rest.next())
{
int iid=rest.getInt("id");
String username=rest.getString("username");
String password=rest.getString("password");
usersList.add(new User(iid,username,password));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
db.DBclose();
}
return usersList;
}
@Override
public List<User> findAll() {
List<User> usersList = new ArrayList<User>();
String selectUsers="select * from users";
// 封装参数
// Object[] selparams = { 1 };
//2.执行并接收查询结果
ResultSet rest= db.execQueryAll(selectUsers);
try {
while(rest.next())
{
int iid=rest.getInt("id");
String username=rest.getString("username");
String password=rest.getString("password");
usersList.add(new User(iid,username,password));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
db.DBclose();
}
return usersList;
}
}
com.java.DB包
com.java.DB.DBUtilConfig
package com.java.DB;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//DBUtilConfig读取配置文件
public class DBUtilConfig {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
protected Connection conn;
//静态代码块,在类初次被加载的时候执行且仅会被执行一次
static {
try {
// 在Java中,其配置文件常为.properties文件,是以键值对的形式进行参数配置的。
// 用Properties类来读取.properties文件数据
// 步骤说明:1.调用类加载器的方法加载资源,返回的字节流
// DBUtilConfig.class是获得当前对象所属的class对象
// getClassLoader()是取得该Class对象的类装载器
// getResourceAsStream(“database.properties”)
InputStream ins = DBUtilConfig.class.getClassLoader().getResourceAsStream("database.properties");
// 2.使用Properties类,从.properties属性文件对应的文件输入流中,加载属性列表到Properties类对象,
Properties props = new Properties();
props.load(ins);
// 3.通过getProperty方法用指定的键在此属性列表中搜索属性, 动态获取.properties配置文件中的数据
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
// 4、加载驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
// 3.执行更新数据库的预编译SQL命令
public boolean execUpdate(String sql, Object[] params) {
try {
conn = getConnection();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
// 1.创建预编译命令对象
PreparedStatement stmt = conn.prepareStatement(sql);
// 2.设置为字段匹配参数
for (int i = 0; params != null && i < params.length; i++) {
Object param = params[i];
if (param instanceof Integer) {
stmt.setInt(i + 1, Integer.parseInt(param.toString()));
}
if (param instanceof Float) {
stmt.setFloat(i + 1, Float.parseFloat(param.toString()));
}
if (param instanceof Double) {
stmt.setDouble(i + 1, Double.parseDouble(param.toString()));
}
if (param instanceof String) {
stmt.setString(i + 1, param.toString());
}
}
// 3.执行预编译命令
if (stmt.executeUpdate() > 0) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return false;
}
// 4.执行预编译查询
public ResultSet execQueryById(String sql, Object[] params) {
try {
conn = getConnection();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
// 1.创建预编译命令对象
PreparedStatement stmt = conn.prepareStatement(sql);
// 2.设置为字段匹配参数
for (int i = 0; params != null && i < params.length; i++) {
Object param = params[i];
if (param instanceof Integer) {
stmt.setInt(i + 1, Integer.parseInt(param.toString()));
}
if (param instanceof Float) {
stmt.setFloat(i + 1, Float.parseFloat(param.toString()));
}
if (param instanceof Double) {
stmt.setDouble(i + 1, Double.parseDouble(param.toString()));
}
if (param instanceof String) {
stmt.setString(i + 1, param.toString());
}
}
// 3.执行预编译命令
return stmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 4.执行查询全部
public ResultSet execQueryAll(String sql) {
try {
conn = getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
try {
Statement stmt = conn.createStatement();
// PreparedStatement stmt = conn.prepareStatement(sql);
return stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 5.关闭数据库
public void DBclose() {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
com.java.Entity包
com.java.Entity.Course
package com.java.Entity;
/**
* @projectName:
* @package: com.java.Entity
* @className: Course
* @author: GCT
* @description: TODO
* @date: 2022/9/26 19:48
* @version: 1.0
*/
public class Course {
private int id;
private String cname;
private int credit;
private String department;
private String content;
public Course() {
}
public Course(String cname, int credit, String department, String content) {
this.cname = cname;
this.credit = credit;
this.department = department;
this.content = content;
}
public Course(int id, String cname, int credit, String department, String content) {
this.id = id;
this.cname = cname;
this.credit = credit;
this.department = department;
this.content = content;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public int getCredit() {
return credit;
}
public void setCredit(int credit) {
this.credit = credit;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
@Override
public String toString() {
return "Course{" +
"id=" + id +
", cname='" + cname + '\'' +
", credit=" + credit +
", department='" + department + '\'' +
", content='" + content + '\'' +
'}';
}
}
com.java.Entity.User
package com.java.Entity;
// User实体类,针对数据表User,实现数据表到类结构的转换,供 DAO层使用
public class User {
private int id;// 主键
private String username;// 用户名
private String password;// 密码
public User(int id, String username, String password) {
super();
this.id = id;
this.username = username;
this.password = password;
}
public User(String username, String password) {
super();
this.username = username;
this.password = password;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
src/main/resources包
src/main/resources/database.properties
#mysql8
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
#password=root
password=你的密码
src/test包
src/test/java/TestMavenCourseDao.java
import com.java.Dao.CourseDao;
import com.java.Dao.Impl.CourseDaoImpl;
import com.java.Entity.Course;
import java.util.List;
/**
* @projectName:
* @package: PACKAGE_NAME
* @className: TestMavenCourseDao
* @author: GCT
* @description: TODO
* @date: 2022/9/26 18:46
* @version: 1.0
*/
public class TestMavenCourseDao {
public static void main(String[] args) {
CourseDao courseDao = new CourseDaoImpl();
// ===============测试插入语句===============
courseDao.add(new Course("test",3,"test","test"));
// ===============测试查询语句===============
// 根据传入的Id值返回表中对应id的行内容
System.out.println("=============findById==============");
List<Course> courseListById = courseDao.findById(2);
for (Course item:courseListById){
System.out.println(item.toString());
}
System.out.println("=============findAll()==============");
List<Course> courseListAll1 = courseDao.findAll();
for (Course item:courseListAll1){
System.out.println(item.toString());
}
// ===============测试删除语句===============
courseDao.deleteById(8);
System.out.println("=============deleteById后findAll()==============");
List<Course> courseListAll2 = courseDao.findAll();
for (Course item:courseListAll2){
System.out.println(item.toString());
}
// ===============测试更新语句===============
courseDao.update(new Course(10,"test_update",6666,"test_update","test_update"));
System.out.println("=============update后findAll()==============");
List<Course> courseListAll3 = courseDao.findAll();
for (Course item:courseListAll3){
System.out.println(item.toString());
}
}
}
src/test/java/TestMavenUserDao.java
import com.java.Dao.CourseDao;
import com.java.Dao.Impl.CourseDaoImpl;
import com.java.Dao.Impl.UserDaoImpl;
import com.java.Dao.UserDao;
import com.java.Entity.Course;
import com.java.Entity.User;
import java.util.List;
/**
* @projectName:
* @package: PACKAGE_NAME
* @className: TestMavenUserDao
* @author: GCT
* @description: TODO
* @date: 2022/9/26 21:27
* @version: 1.0
*/
public class TestMavenUserDao {
public static void main(String[] args) {
UserDao userDao = new UserDaoImpl();
// ===============测试插入语句===============
userDao.add(new User("test","test"));
// ===============测试查询语句===============
// 根据传入的Id值返回表中对应id的行内容
System.out.println("=============findById==============");
List<User> userListById = userDao.findById(2);
for (User item:userListById){
System.out.println(item.toString());
}
System.out.println("=============findAll()==============");
List<User> userListAll1 = userDao.findAll();
for (User item:userListAll1){
System.out.println(item.toString());
}
// ===============测试删除语句===============
userDao.deleteById(10);
System.out.println("=============deleteById后findAll()==============");
List<User> userListAll2 = userDao.findAll();
for (User item:userListAll2){
System.out.println(item.toString());
}
// ===============测试更新语句===============
userDao.update(new User(3,"test6666","test6666"));
System.out.println("=============update后findAll()==============");
List<User> userListAll3 = userDao.findAll();
for (User item:userListAll3){
System.out.println(item.toString());
}
}
}
pom.xml
注意项目名和mysql驱动的版本要根据自己的项目修改
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>项目名</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<!-- 我本地的mysql版本装的是8.0.17,所以导入对应版本的驱动-->
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
</project>
6、运行结果
TestMavenCourseDao.java
TestMavenUserDao.java
7、数据库表结构
本项目中数据库表的创建:在可视化工具中运行资料中的myschool.sql就ok了
比如navicat中:
找到myschool.sql文件并运行就行了
基本结构: