【Bootstrap4前端框架+MySQL數(shù)據(jù)庫】前后端綜合實訓【10天課程 博客匯總表 詳細筆記】

目 ? 錄

MySQL數(shù)據(jù)庫——建庫、建表

新建連接、測試連接

新建news_manager數(shù)據(jù)庫

新建5張數(shù)據(jù)表

新建5張數(shù)據(jù)表的SQL語句

新建架構(gòu)設(shè)計器（查看表與表之間的關(guān)系）

eclipse——JDBC開發(fā)

新建Java項目

格式化代碼

添加數(shù)據(jù)庫驅(qū)動jar包

構(gòu)建路徑->配置構(gòu)建路徑：Junit 4（Java自帶的測試框架，測試效率、結(jié)果）

建包、類說明

package com.newcapec.dao; // dao層包（模板Dao）模板代碼、固定寫法

BaseDao.java：應(yīng)用于5個數(shù)據(jù)表的Java接口

NewsInfoDao.java：增刪改查

UserInfoDao.java：增刪改查

package com.newcapec.entity; // 實體entity

NewsInfoEntity.java：新聞實體類

UserInfoEntity.java：管理員實體類

package com.newcapec.test; //?測試包

TestUserInfoDao.java

package com.newcapec.utils; //?工具包

DBUtils.java：獲取數(shù)據(jù)庫連接的工具類

---

MySQL數(shù)據(jù)庫——建庫、建表

新建連接、測試連接

新建news_manager數(shù)據(jù)庫

新建5張數(shù)據(jù)表

新建5張數(shù)據(jù)表的SQL語句

/* SQLyog Ultimate v11.22 (64 bit) MySQL - 5.5.56 : Database - news_manager ********************************************************************* *//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`news_manager` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `news_manager`;/*Table structure for table `item_user` */DROP TABLE IF EXISTS `item_user`;CREATE TABLE `item_user` (`item_user_id` int(11) NOT NULL COMMENT '用戶欄目關(guān)系主鍵',`user_id` int(11) DEFAULT NULL COMMENT '用戶主鍵ID',`item_id` int(11) DEFAULT NULL COMMENT '欄目ID',`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',PRIMARY KEY (`item_user_id`),KEY `FK_Reference_1` (`user_id`),KEY `FK_Reference_2` (`item_id`),CONSTRAINT `FK_Reference_2` FOREIGN KEY (`item_id`) REFERENCES `news_item` (`item_id`),CONSTRAINT `FK_Reference_1` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='欄目與用戶之間的關(guān)系表';/*Data for the table `item_user` *//*Table structure for table `logs_info` */DROP TABLE IF EXISTS `logs_info`;CREATE TABLE `logs_info` (`logs_id` int(11) NOT NULL,`user_id` int(11) DEFAULT NULL COMMENT '用戶主鍵ID',`logs_content` char(10) DEFAULT NULL,`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',PRIMARY KEY (`logs_id`),KEY `FK_Reference_4` (`user_id`),CONSTRAINT `FK_Reference_4` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='操作日志表';/*Data for the table `logs_info` *//*Table structure for table `news_info` */DROP TABLE IF EXISTS `news_info`;CREATE TABLE `news_info` (`news_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '新聞主鍵',`item_id` int(11) DEFAULT NULL COMMENT '欄目ID',`news_title` varchar(255) NOT NULL COMMENT '新聞標題',`news_image` varchar(255) DEFAULT NULL COMMENT '新聞圖片',`news_content` text COMMENT '新聞內(nèi)容',`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',PRIMARY KEY (`news_id`),KEY `FK_Reference_3` (`item_id`),CONSTRAINT `FK_Reference_3` FOREIGN KEY (`item_id`) REFERENCES `news_item` (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='新聞詳情表';/*Data for the table `news_info` *//*Table structure for table `news_item` */DROP TABLE IF EXISTS `news_item`;CREATE TABLE `news_item` (`item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '欄目ID',`item_name` varchar(255) NOT NULL COMMENT '欄目名稱',`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',PRIMARY KEY (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='新聞欄目';/*Data for the table `news_item` *//*Table structure for table `user_info` */DROP TABLE IF EXISTS `user_info`;CREATE TABLE `user_info` (`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶主鍵ID',`user_name` varchar(255) NOT NULL COMMENT '用戶名',`user_pwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用戶密碼',`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用戶信息表';/*Data for the table `user_info` */insert into `user_info`(`user_id`,`user_name`,`user_pwd`,`create_time`,`update_time`) values (1,'張三','123','2020-11-19 14:30:51','2020-11-19 14:30:55');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

新建架構(gòu)設(shè)計器（查看表與表之間的關(guān)系）

eclipse——JDBC開發(fā)

新建Java項目

? ??

格式化代碼

添加數(shù)據(jù)庫驅(qū)動jar包

jar包可以從MySQL官網(wǎng)下載。

? ??

構(gòu)建路徑->配置構(gòu)建路徑：Junit 4（Java自帶的測試框架，測試效率、結(jié)果）

建包、類說明

? ?

• package com.newcapec.dao; ? ?：dao層包（模板Dao）模板代碼、固定寫法
• package com.newcapec.entity; ：實體entity
• package com.newcapec.test;? ? ：測試包
• package com.newcapec.utils;? ?：工具包

package com.newcapec.dao; // dao層包（模板Dao）模板代碼、固定寫法

BaseDao.java：應(yīng)用于5個數(shù)據(jù)表的Java接口

BaseDao<T>：<T>是泛型，應(yīng)用于5個數(shù)據(jù)表的Java接口。

package com.newcapec.dao;import java.sql.SQLException; import java.util.List;public interface BaseDao<T> {public boolean insert(T t) throws ClassNotFoundException, SQLException;public boolean deleteById(int id) throws ClassNotFoundException, SQLException;public boolean update(T t) throws ClassNotFoundException, SQLException;public T selectById(int id) throws ClassNotFoundException, SQLException;public List<T> selectAll() throws ClassNotFoundException, SQLException; }

NewsInfoDao.java：增刪改查

package com.newcapec.dao;import java.util.List;import com.newcapec.entity.NewsInfoEntity;public class NewsInfoDao implements BaseDao<NewsInfoEntity> {@Overridepublic boolean insert(NewsInfoEntity t) {// TODO 自動生成的方法存根return false;}@Overridepublic boolean deleteById(int id) {// TODO 自動生成的方法存根return false;}@Overridepublic boolean update(NewsInfoEntity t) {// TODO 自動生成的方法存根return false;}@Overridepublic NewsInfoEntity selectById(int id) {// TODO 自動生成的方法存根return null;}@Overridepublic List<NewsInfoEntity> selectAll() { /*全查詢*/// TODO 自動生成的方法存根return null;}}

UserInfoDao.java：增刪改查

package com.newcapec.dao;import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List;import com.newcapec.entity.UserInfoEntity; import com.newcapec.utils.DBUtils;public class UserInfoDao implements BaseDao<UserInfoEntity>{@Overridepublic boolean insert(UserInfoEntity t) throws ClassNotFoundException, SQLException {boolean flag = false;// 獲取與數(shù)據(jù)庫的連接Connection connection = DBUtils.openConn();// ?表示占位符 可以解決sql注入的問題String sql = "insert into user_info (user_name,user_pwd,create_time) values (?,?,now())";PreparedStatement statement = connection.prepareStatement(sql);statement.setString(1, t.getUserName());statement.setString(2, t.getUserPwd());// 執(zhí)行sql語句int count = statement.executeUpdate();if (count > 0) {flag = true;}statement.close();connection.close();return flag;}@Overridepublic boolean deleteById(int id) throws ClassNotFoundException, SQLException {boolean flag = false;//獲取連接Connection connection = DBUtils.openConn();//sql語句String sql = "delete from user_info where user_id = ?";//預(yù)處理對象PreparedStatement statement = connection.prepareStatement(sql);//添加參數(shù)statement.setInt(1, id);//執(zhí)行預(yù)處理對象int count = statement.executeUpdate();if(count > 0) {flag = true;}statement.close();connection.close();return flag;}@Overridepublic boolean update(UserInfoEntity t) throws ClassNotFoundException, SQLException {boolean flag = false;//獲取連接Connection connection = DBUtils.openConn();//sql語句String sql = "update user_info set user_name = ?,user_pwd = ? where user_id = ?";//預(yù)處理對象PreparedStatement statement = connection.prepareStatement(sql);//添加參數(shù)statement.setString(1, t.getUserName());statement.setString(2, t.getUserPwd());statement.setInt(3, t.getUserId());//執(zhí)行預(yù)處理對象int count = statement.executeUpdate();if(count > 0) {flag = true;}statement.close();connection.close();return flag;}@Overridepublic UserInfoEntity selectById(int id) throws ClassNotFoundException, SQLException {UserInfoEntity userInfoEntity = null;Connection connection = DBUtils.openConn();String sql = "select * from user_info where user_id = ?";PreparedStatement statement = connection.prepareStatement(sql);statement.setInt(1, id);ResultSet resultSet = statement.executeQuery();while(resultSet.next()) {int userId = resultSet.getInt("user_id");String userName = resultSet.getString("user_name");String userPwd = resultSet.getString("user_pwd");Date createTime = resultSet.getTimestamp("create_time");Date updateTime = resultSet.getTimestamp("update_time");userInfoEntity = new UserInfoEntity(userId, userName, userPwd, createTime, updateTime);}resultSet.close();statement.close();connection.close();return userInfoEntity;}@Overridepublic List<UserInfoEntity> selectAll() throws ClassNotFoundException, SQLException { /*全查詢*/List<UserInfoEntity> list = new ArrayList<>();Connection connection = DBUtils.openConn();String sql = "select * from user_info";PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();while(resultSet.next()) {int userId = resultSet.getInt("user_id");String userName = resultSet.getString("user_name");String userPwd = resultSet.getString("user_pwd");Date createTime = resultSet.getTimestamp("create_time");Date updateTime = resultSet.getTimestamp("update_time");UserInfoEntity userInfoEntity = new UserInfoEntity(userId, userName, userPwd, createTime, updateTime);list.add(userInfoEntity);}resultSet.close();statement.close();connection.close();return list;}}

package com.newcapec.entity; // 實體entity

NewsInfoEntity.java：新聞實體類

package com.newcapec.entity;public class NewsInfoEntity {}

UserInfoEntity.java：管理員實體類

package com.newcapec.entity;import java.util.Date;public class UserInfoEntity {private int userId;private String userName;private String userPwd;private Date createTime;private Date updateTime;public UserInfoEntity() {}public UserInfoEntity(int userId, String userName, String userPwd, Date createTime, Date updateTime) {super();this.userId = userId;this.userName = userName;this.userPwd = userPwd;this.createTime = createTime;this.updateTime = updateTime;}public int getUserId() {return userId;}public void setUserId(int userId) {this.userId = userId;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getUserPwd() {return userPwd;}public void setUserPwd(String userPwd) {this.userPwd = userPwd;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}public Date getUpdateTime() {return updateTime;}public void setUpdateTime(Date updateTime) {this.updateTime = updateTime;}@Overridepublic String toString() {return "UserInfoEntity [userId=" + userId + ", userName=" + userName + ", userPwd=" + userPwd + ", createTime="+ createTime + ", updateTime=" + updateTime + "]";}}

package com.newcapec.test; //?測試包

TestUserInfoDao.java

package com.newcapec.test;import java.sql.SQLException; import java.util.List;import org.junit.Assert; import org.junit.Test;import com.newcapec.dao.UserInfoDao; import com.newcapec.entity.UserInfoEntity;public class TestUserInfoDao {private UserInfoDao userInfoDao = new UserInfoDao();@Test /*Test注解*/public void testInsert() throws ClassNotFoundException, SQLException {UserInfoEntity userInfoEntity = new UserInfoEntity();userInfoEntity.setUserName("田七");userInfoEntity.setUserPwd("123456");// userInfoDao.insert(userInfoEntity);Assert.assertTrue(userInfoDao.insert(userInfoEntity));}@Testpublic void testSelect() throws ClassNotFoundException, SQLException {List<UserInfoEntity> list = userInfoDao.selectAll();System.out.println(list);}@Testpublic void testDeleteById() throws ClassNotFoundException, SQLException {Assert.assertTrue(userInfoDao.deleteById(7));}@Testpublic void testUpdate() throws ClassNotFoundException, SQLException {UserInfoEntity userInfoEntity = userInfoDao.selectById(1);userInfoEntity.setUserName("zhangsan");userInfoDao.update(userInfoEntity);}@Testpublic void testselectById() throws ClassNotFoundException, SQLException {System.out.println(userInfoDao.selectById(1));}}

package com.newcapec.utils; //?工具包

DBUtils.java：獲取數(shù)據(jù)庫連接的工具類

建議使用java.sql包下的接口：代碼適應(yīng)性更好。

package com.newcapec.utils;import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException;import com.mysql.jdbc.Driver;public class DBUtils {public static Connection openConn() throws ClassNotFoundException, SQLException {// 連接數(shù)據(jù)庫四要素// url username password driverString user = "root";String password = "123456";String driver = "com.mysql.jdbc.Driver"; // 數(shù)據(jù)庫驅(qū)動String url = "jdbc:mysql://127.0.0.1:3306/news_manager?characterEncoding=UTF-8";// 注冊數(shù)據(jù)庫驅(qū)動Class.forName(driver);return DriverManager.getConnection(url, user, password);} }

今天上午主要講了jdbc的知識。下午沒課（公休）...

總結(jié)

以上是生活随笔為你收集整理的Bootstrap4+MySQL前后端综合实训-Day05-AM【MySQL数据库(SQLyog软件基本操作、架构设计器)、eclipse(JDBC开发-添加驱动、构建路径、增删改查基本测试)】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。