JavaWeb课堂测试（一）
一、数据库构建
创建一个用户表，存储用户名和密码
创建数据表，存储数据
二、jdbc连接数据库
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Util {
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Connection con = null;
Statement sta = null;
ResultSet res = null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test ?useUnicode=true&characterEncoding=utf8", "root", "123456");
return con;
}
public static void close(Connection con) {
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(PreparedStatement pre) {
try {
if (pre != null) {
pre.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(ResultSet res) {
try {
if (res != null) {
res.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
三、创建JavaBean组件
package bean;
public class NewsBean {
private String title; //标题
private String keyword; //关键字
private String author; //作者
private String date; //日期
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
}
package bean;
public class UserBean {
private String name; //用户名
private String password; //密码
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
三、创建DAO类接口
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import bean.NewsBean;
import bean.LookBean;
import util.Util;
public class NewsDao {
//添加新闻信息
public int add(NewsBean bean)throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
try {
String sql = "insert into news1(title,keyword,author,date) values(?,?,?,?)";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getTitle());
pre.setString(2, bean.getKeyword());
pre.setString(3, bean.getAuthor());
pre.setString(4, bean.getDate());
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
}
return 1;
}
//删除新闻信息
public int delete(NewsBean bean) throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
try {
String sql="delete from news1 where title=?";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getTitle());
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
}
return 1;
}
//修改新闻信息
public int update(NewsBean bean) throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
try {
String sql = "update news1 set keyword=?,author=?,date=?, where title=?";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getTitle());
pre.setString(2, bean.getKeyword());
pre.setString(3, bean.getAuthor());
pre.setString(4, bean.getDate());
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
}
return 1;
}
//查询新闻信息
public int read(NewsBean bean) throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
ResultSet resultSet=null;
try {
String sql = "select * from news1 where title=?";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getTitle());
resultSet = pre.executeQuery();
while (resultSet.next()){
System.out.println("title"+resultSet.getString("title")+"keyword"+resultSet.getString("keyword")+"author"+resultSet.getString("author")+"date"+resultSet.getString("date"));
}
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
Util.close(resultSet);
}
return 1;
}
}
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import bean.UserBean;
import util.Util;
public class UserDao {
//添加用户信息
public int add(UserBean bean)throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
try {
String sql = "insert into user(name,password) values(?,?)";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getName());
pre.setString(2, bean.getPassword());
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
}
return 1;
}
//删除新闻信息
public int delete(UserBean bean) throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
try {
String sql="delete from user where name=?";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getName());
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
}
return 1;
}
//修改新闻信息
public int update(UserBean bean) throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
try {
String sql = "update user set password=?, where name=?";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getName());
pre.setString(2, bean.getPassword());
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
}
return 1;
}
//查询新闻信息
public int read(UserBean bean) throws ClassNotFoundException, SQLException
{
Connection con = Util.getConnection();
PreparedStatement pre = null;
ResultSet resultSet=null;
try {
String sql = "select * from user where title=?";
pre = con.prepareStatement(sql);
pre.setString(1, bean.getName());
resultSet = pre.executeQuery();
while (resultSet.next()){
System.out.println("name"+resultSet.getString("name")+"password"+resultSet.getString("password"));
}
pre.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
Util.close(con);
Util.close(pre);
Util.close(resultSet);
}
return 1;
}
}
