新闻的增删改查
学习了一些博客知识【结尾会贴上，大家可以参考】在借鉴的基础上又进行了代码的优化，从而实现了CRUD，下面分享我的代码过程：
包结构，是为了方便管理，所以我们要这样进行设计：
在数据库进行表结构的插入和设计：（目前小辣鸡制作了新闻的crud）
接着我们进行类的实现，即完善domain类
public class NoteEntity {
private int id;
private String type;
private String text;
public NoteEntity() {
}
public NoteEntity(int id, String type, String text) {
this.id = id;
this.type = type;
this.text = text;
}
@Override
public String toString() {
return "NoteEntity{" +
"id='" + id + '\'' +
", type='" + type + '\'' +
", text='" + text + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
}
实现了类的实现，接着进行数据库的连接（根据我查到的资料，应该是封闭在一个jdbc.Properties文件中是最好的，但我还没做）
public class jdbcUtil {
public static final String url = "jdbc:mysql://localhost:3306/*?useSSL=false&serverTimezone=UTC";
public static final String user = "******";
public static final String password = "******";
public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
/**
* 获取Connection对象
*
* @return
*/
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// /**
// * 释放资源
// */
public static void ReleaseResource(Connection connection, PreparedStatement pstm, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
然后进行crud代码的实现
（建议写一个接口类，会方便很多）
public Connection conn1=null;
public ResultSet rs=null;
public PreparedStatement ps=null;
//查询所有的数据
@Override
public List<NoteEntity> queryAllData() {
conn1=jdbcUtil.getConnection();//连接数据库
List<NoteEntity> list=new ArrayList<NoteEntity>();
try{
String sql="select * from note";//查询多条数据
ps=conn1.prepareStatement(sql);
rs=ps.executeQuery();
NoteEntity note=null;
while(rs.next()){
note =new NoteEntity();
note.setId(Integer.parseInt(rs.getString("id")));
note.setType(rs.getString("type"));
note.setText(rs.getString("text"));
list.add(note);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
jdbcUtil.ReleaseResource(conn1,ps,rs);
}
return list;
}
//新增
@Override
public int insertData(NoteEntity note) {
conn1=jdbcUtil.getConnection();
int i=0;
try{
String sql="insert into note(type,text) values(?,?)";
ps=conn1.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
// ps.setObject(1,note.getId());
ps.setObject(1,note.getType());
ps.setObject(2,note.getText());
ps.executeUpdate();
rs=ps.getGeneratedKeys();//得到最新的ID
if(rs.next()){
i=rs.getInt(1);
}
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtil.ReleaseResource(conn1,ps,rs);
}
return i;
}
//修改
@Override
public int updateData(NoteEntity note) {
conn1=jdbcUtil.getConnection();
int i=0;
try{
String sql="update note set type=?,text=? where id=?";
ps=conn1.prepareStatement(sql);
ps.setObject(1,note.getType());
ps.setObject(2,note.getText());
ps.setInt(3,note.getId());
i=ps.executeUpdate();
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtil.ReleaseResource(null,ps,rs);
}
return i;
}
@Override
public int deleteData(int id) {
conn1=jdbcUtil.getConnection();
int i=0;
try{
String sql="delete from note where id=?";
ps=conn1.prepareStatement(sql);
ps.setInt(1,id);
i=ps.executeUpdate();
if(i==1){
return i;
}
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtil.ReleaseResource(null,ps,rs);
}
System.out.println("未找到数据！");
return i;
}
//查询一条数据通过ID
@Override
public NoteEntity queryDataById(int id) {
conn1=jdbcUtil.getConnection();
NoteEntity note=null;
if(id>0) {
try {
String sql = "select * from note where id=?";
ps = conn1.prepareStatement(sql);
ps.setObject(1, id);
rs = ps.executeQuery();
if (rs.next()) {
note = new NoteEntity();
note.setId(rs.getInt("id"));
note.setType(rs.getString("type"));
note.setText(rs.getString("text"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcUtil.ReleaseResource(null, ps, rs);
}
}
return note;
接着做service层和servlet层
public Connection conn=null;
public ResultSet rs=null;
public PreparedStatement ps=null;
boolean b=false;
// 这里service层要想与dao层建立联系那么必须创建dao层的对象
NoteDao note=new NoteDaoImpl();
// 查询所有的数据
@Override
public List<NoteEntity> queryAllData() {
return note.queryAllData();
}
// 新增
@Override
public Boolean insertData(NoteEntity no) {
if(no!=null) {
note.insertData(no);
b = true;
}
return b;
}
// 修改
@Override
public Boolean updateData(NoteEntity no) {
if(no!=null) {
note.updateData(no);
b=true;
}
return b;
}
// 删除
@Override
public Boolean deleteData(int id) {
if(id!=0) {
note.deleteData(id);
b=true;
}
return b;
}
// 查询一条数据
@Override
public NoteEntity queryDataById(int id) {
if (id != 0) {
return note.queryDataById(id);
}
else {
return null;
}
}
private static final long serialVersionUID = 1l;
// 建立一个service对象,为了与service层建立联系
NoteService note = new NoteServiceImpl();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");//防止乱码
String type = req.getParameter("action");
// 新增
if ("Insert".equals(type)) {
insert(req, resp);
} else if ("Update".equals(type)) {
update(req, resp);
} else if ("QueryById".equals(type)) {
queryById(req, resp);
} else if ("Delete".equals(type)) {
delete(req, resp);
} else if ("QueryAll".equals(type)) {
queryAll(req, resp);
}
}
public void insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// Integer id = Integer.valueOf(request.getParameter("id"));
String type = request.getParameter("type");
String text = request.getParameter("text");
// 把这些获取的值放到note里面
NoteEntity no = new NoteEntity();
// no.setId(id);
no.setType(type);
no.setText(text);
// 最后调用服务来添加
String msg = null;
if (note.insertData(no) == true) {
queryAll(request, response);
} else {
msg = "新增失败!!!";
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
}
public void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// Integer id = Integer.valueOf(request.getParameter("id"));
int id= Integer.parseInt(request.getParameter("id"));
String type = request.getParameter("type");
String text = request.getParameter("text");
NoteEntity no = new NoteEntity();
no.setId(id);
no.setType(type);
no.setText(text);
boolean b = note.updateData(no);
if (b) {
queryAll(request, response);
} else {
request.setAttribute("msg", "修改失败!!");
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
}
public void queryById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
NoteEntity no = null;
//Integer id= Integer.valueOf(request.getParameter("id"));
int id= Integer.parseInt(request.getParameter("id"));
System.out.println(id);
no = note.queryDataById(id);
request.setAttribute("note", no);
request.getRequestDispatcher("/note.jsp").forward(request, response);
}
public void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// Integer id = Integer.valueOf(request.getParameter("id"));
// System.out.println(id);
String id=request.getParameter("id");
// boolean message=note.deleteData(id);
boolean message= note.deleteData(Integer.parseInt(id));
if(message==true){
queryAll(request,response);
}else {
request.setAttribute("msg","删除失败!!!");
request.getRequestDispatcher("/index.jsp").forward(request,response);
}
}
public void queryAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<NoteEntity> list = note.queryAllData();
request.setAttribute("list", list);
request.getRequestDispatcher("note.jsp").forward(request, response);
}
这样就实现了最基本的增删改查了！
那么记下来我想要优化的目标有：
1、实现css的样式美化
2、优化数据库连接代码
3、功能更加完善（创建多个页面实现页面之间的跳转）
4、为了更好的管理jar包，学会maven
5、创建baseDao和baseServlet类
持续更新中...
标签：note,JDBC,ps,request,public,新手入门,jsp,type,id From： https://www.cnblogs.com/gbrr/p/17142154.html