现我们数据库中已有的一张新闻表news，结构如下：根据表结构我们创建一个News对象：将下面代码补充完整，完成数据库连接、删除、更新以及查找方法。其中删除方法是通过用户传入新闻id来进行删除
题目
1. public class News {
2. private int id;
3. private String title;
4. private String author_name;
5. public News() {
6. super();
7. }
8. public News(int id, String title, String author_name) {
9. super();
10. this.id = id;
11. this.title = title;
12. this.author_name = author_name;
13. }
14. public int getId() {
15. return id;
16. }
17. public void setId(int id) {
18. this.id = id;
19. }
20. public String getTitle() {
21. return title;
22. }
23. public void setTitle(String title) {
24. this.title = title;
25. }
26. public String getAuthor_name() {
27. return author_name;
28. }
29. public void setAuthor_name(String author_name) {
30. this.author_name = author_name;
31. }
32. @Override
33. public String toString() {
34. return "News [id=" + id + ", title=" + title + ", author_name="
35. + author_name + "]";
36. }
37. }
/**将下面代码补充完整，完成数据库连接、删除、更新以及查找方法。其中删除方法是通过用户传入新闻id来进行删除。
注意：连接数据库名为mysql_db*/
mport java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import test.News;
public class JDBCUtils {
/**
* 连接数据库
*/
private static Connection getConnection() {
Connection conn=null;
String url="";
try {
}catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
/********** End **********/
return conn;
}
public void update(News news) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
/********** Begin **********/
String sql = "";
try{
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("更新数据失败");
}finally{
close(null, ps, conn);
}
/********** End **********/
}
public List<News> findAll() throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
News news = null;
List<News> newsList = new ArrayList<News>();
/********** Begin **********/
String sql = "";
try{
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("查询所有数据失败");
}finally{
close(rs, ps, conn);
}
/********** End **********/
return newsList;
}
public void delete(int id) throws SQLException{
Connection conn = getConnection();
PreparedStatement ps = null;
/********** Begin **********/
String sql = "";
try{
}catch(SQLException e){
e.printStackTrace();
throw new SQLException(" 删除数据失败");
}
finally{
close(null, ps, conn);
}
/********** End **********/
}
public void insert(News news) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
String sql = " ";
try{
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("添加数据失败");
}finally{
close(null, ps, conn);
}
}
public News findById(int id) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
News news = null;
String sql = "";
try{
}
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("根据ID查询数据失败");
}
finally{
close(rs, ps, conn);
}
return news;
}
public static void close(ResultSet rs,PreparedStatement ps,Connection conn){
try {
if(rs!=null)rs.close();
if(ps!=null)ps.close();
if(conn!=null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
运行截图：
建库建表
增删改查
只放一张查询所有数据的截图
源代码
建库建表的代码
create database mysql_db;
use mysql_db;
dorp table if exists news;
create table news(
id int primary key,
title varchar(60),
author_name varchar(30)
)
insert into news(id,title,author_name)values
(01,'厂里投资100万翻修冬寺','李华'),
(02,'小炒黄牛肉创始人如是说','王利'),
(03,'3分钟教会你操作系统','宫迁');
java代码
package com.serein.database_operate;
import org.testng.annotations.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCUtils {
public static void main(String[] args) throws Exception {
// findAll();
// insert(04);
// update("5分钟教会你数据结构");
findById(1);
// delete(4);
}
private static Connection getConnection1() {
Connection conn=null;
String url="jdbc:mysql://127.0.0.1:3306/mysql_db?useSSL=false";
try {
String username = "xxxxx";
String password = "xxxxx";
conn = DriverManager.getConnection(url, username, password);
}catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void update(String news) throws SQLException {
Connection conn = getConnection1();
PreparedStatement ps = null;
String sql = "update news set title = ? where id = 3;";
try {
ps = conn.prepareStatement(sql);
ps.setString(1, news);
int count = ps.executeUpdate();
System.out.println("受影响的行数：" + count);
} catch (SQLException e) {
e.printStackTrace();
throw new SQLException("更新数据失败");
} finally {
close(null, ps, conn);
}
}
public static List<News> findAll() throws SQLException {
Connection conn = getConnection1();
PreparedStatement ps = null;
ResultSet rs = null;
News news = null;
List<News> newsList = new ArrayList<News>();
String sql = "select * from news";
try{
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String title = rs.getString("title");
String author_name = rs.getString("author_name");
news = new News();
news.setId(id);
news.setTitle(title);
news.setAuthor_name(author_name);
newsList.add(news);
}
System.out.println(newsList);
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("查询所有数据失败");
}finally{
close(rs, ps, conn);
}
return newsList;
}
public static void delete(int id) throws SQLException{
Connection conn = getConnection1();
PreparedStatement ps = null;
String sql = "delete from news where id = ?";
try{
ps = conn.prepareStatement(sql);
ps.setInt(1,id);
int count = ps.executeUpdate();
System.out.println("受影响的行数" + count);
}catch(SQLException e){
e.printStackTrace();
throw new SQLException(" 删除数据失败");
}
finally{
close(null, ps, conn);
}
}
public static void insert(int news) throws SQLException {
Connection conn = getConnection1();
PreparedStatement ps = null;
String sql = "insert into news (id,title,author_name) values(?,'论java基础的重要性','张冲')";
// (04,'论java基础的重要性','张冲')
try{
ps = conn.prepareStatement(sql);
ps.setInt(1,news);
int count = ps.executeUpdate();
System.out.println("受影响的行数" + count);
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("添加数据失败");
}finally{
close(null, ps, conn);
}
}
public static News findById(int id) throws SQLException {
Connection conn = getConnection1();
PreparedStatement ps = null;
ResultSet rs = null;
News news = null;
List<News> newsList1 = new ArrayList<News>();
String sql = "select * from news where id = ?;";
// try{
// ps = conn.prepareStatement(sql);
// ps.setInt(1, id);
// ps.executeQuery();
//
// }catch(SQLException e){
// e.printStackTrace();
// throw new SQLException("根据ID查询数据失败");
// }
// finally{
// close(rs, ps, conn);
// }
try{
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()){
int id1 = rs.getInt("id");
String title1 = rs.getString("title");
String author_name1 = rs.getString("author_name");
news = new News();
news.setId(id1);
news.setTitle(title1);
news.setAuthor_name(author_name1);
newsList1.add(news);
}
System.out.println(newsList1);
}catch(SQLException e){
e.printStackTrace();
throw new SQLException("查询所有数据失败");
}finally{
close(rs, ps, conn);
}
return news;
}
public static void close(ResultSet rs, PreparedStatement ps, Connection conn){
try {
if(rs!=null)rs.close();
if(ps!=null)ps.close();
if(conn!=null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
写在最后
把sql账户密码改成自己账户密码即可。增删改查方法我都放在main方法里面了，一次调用一个，调用某一个方法时建议把其他方法注释掉。每运行一个方法后，可打开Navicat看看该操作是否生效。
如果觉得本文有帮助，欢迎评论点赞转发哦！你们的支持是我更新的最大动力！
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
