1200字范文 > Bootstrap4+MySQL前后端综合实训-Day05-AM【MySQL数据库(SQLyog软件基本操作架构设

Bootstrap4+MySQL前后端综合实训-Day05-AM【MySQL数据库(SQLyog软件基本操作架构设

时间：2019-07-31 00:49:08

【Bootstrap4前端框架+MySQL数据库】前后端综合实训【10天课程博客汇总表详细笔记】

目录

MySQL数据库——建库、建表

新建连接、测试连接

新建news_manager数据库

新建5张数据表

新建5张数据表的SQL语句

新建架构设计器（查看表与表之间的关系）

eclipse——JDBC开发

新建Java项目

格式化代码

添加数据库驱动jar包

构建路径->配置构建路径：Junit 4（Java自带的测试框架，测试效率、结果）

建包、类说明

package com.newcapec.dao; // dao层包（模板Dao）模板代码、固定写法

BaseDao.java：应用于5个数据表的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：获取数据库连接的工具类

MySQL数据库——建库、建表

新建连接、测试连接

新建news_manager数据库

新建5张数据表

新建5张数据表的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 '用户栏目关系主键',`user_id` int(11) DEFAULT NULL COMMENT '用户主键ID',`item_id` int(11) DEFAULT NULL COMMENT '栏目ID',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`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='栏目与用户之间的关系表';/*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 '创建时间',`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 '新闻内容',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`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 '创建时间',`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 '创建时间',`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','-11-19 14:30:51','-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 */;

新建架构设计器（查看表与表之间的关系）

eclipse——JDBC开发

新建Java项目

格式化代码

添加数据库驱动jar包

jar包可以从MySQL官网下载。

构建路径->配置构建路径：Junit 4（Java自带的测试框架，测试效率、结果）

建包、类说明

package com.newcapec.dao; ：dao层包（模板Dao）模板代码、固定写法package com.newcapec.entity; ：实体entitypackage com.newcapec.test; ：测试包package com.newcapec.utils; ：工具包

package com.newcapec.dao; // dao层包（模板Dao）模板代码、固定写法

BaseDao.java：应用于5个数据表的Java接口

BaseDao<T>：<T>是泛型，应用于5个数据表的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;// 获取与数据库的连接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());// 执行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 = ?";//预处理对象PreparedStatement statement = connection.prepareStatement(sql);//添加参数statement.setInt(1, id);//执行预处理对象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 = ?";//预处理对象PreparedStatement statement = connection.prepareStatement(sql);//添加参数statement.setString(1, t.getUserName());statement.setString(2, t.getUserPwd());statement.setInt(3, t.getUserId());//执行预处理对象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：获取数据库连接的工具类

建议使用java.sql包下的接口：代码适应性更好。

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 {// 连接数据库四要素// url username password driverString user = "root";String password = "123456";String driver = "com.mysql.jdbc.Driver"; // 数据库驱动String url = "jdbc:mysql://127.0.0.1:3306/news_manager?characterEncoding=UTF-8";// 注册数据库驱动Class.forName(driver);return DriverManager.getConnection(url, user, password);}}