目 录
构建路径->配置构建路径：Junit 4（Java自带的测试框架，测试效率、结果）
package com.newcapec.dao; // dao层包（模板Dao）模板代码、固定写法
package com.newcapec.entity; // 实体entity
package com.newcapec.test; // 测试包
package com.newcapec.utils; // 工具包
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','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 */;新建架构设计器（查看表与表之间的关系）
eclipse——JDBC开发
新建Java项目
格式化代码
添加数据库驱动jar包
jar包可以从MySQL官网下载。
构建路径->配置构建路径：Junit 4（Java自带的测试框架，测试效率、结果）
建包、类说明
- 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：应用于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> {
@Override
public boolean insert(NewsInfoEntity t) {
// TODO 自动生成的方法存根
return false;
}
@Override
public boolean deleteById(int id) {
// TODO 自动生成的方法存根
return false;
}
@Override
public boolean update(NewsInfoEntity t) {
// TODO 自动生成的方法存根
return false;
}
@Override
public NewsInfoEntity selectById(int id) {
// TODO 自动生成的方法存根
return null;
}
@Override
public 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>{
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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));
}
@Test
public void testSelect() throws ClassNotFoundException, SQLException {
List<UserInfoEntity> list = userInfoDao.selectAll();
System.out.println(list);
}
@Test
public void testDeleteById() throws ClassNotFoundException, SQLException {
Assert.assertTrue(userInfoDao.deleteById(7));
}
@Test
public void testUpdate() throws ClassNotFoundException, SQLException {
UserInfoEntity userInfoEntity = userInfoDao.selectById(1);
userInfoEntity.setUserName("zhangsan");
userInfoDao.update(userInfoEntity);
}
@Test
public 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 driver
String 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);
}
}今天上午主要讲了jdbc的知识。下午没课（公休）...
扫一扫
688
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
