提供五个方法:连接数据库,查询数据所有数据,根据姓名查询相关数据,添加数据,根据id删除数据
需要导入mysql-connector-java-5.1.39-bin这个驱动包 可以自行百度
import java.sql.*;
import java.util.*;
import java.lang.*;
import java.io.*;
import java.util.Date;
import java.text.SimpleDateFormat;
public class JDBC {
public static Connection getConnection() {
Connection st = null;
try {
Class.forName("com.mysql.jdbc.Driver");
try {
st = DriverManager.getConnection("jdbc:mysql://localhost:3306/educationManagement?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "rhjfxy");
return st;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return st;
}
public List getData() {
//遍历所有数据 将 姓名,内容,话题,存入时间 返回
Connection st = getConnection();
List<String> list = new ArrayList<String>();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "SELECT name, neirong, huati,time FROM websites";
try {
ps = st.prepareStatement(sql);
//ps.setObject(1, sno);
// ps.execute();
rs = ps.executeQuery();
while (rs.next()) {
String temp = (rs.getString("websites.name"));
temp += " ";
temp += (rs.getObject("websites.neirong"));
temp += " ";
temp += (rs.getObject("websites.huati"));
temp += " ";
temp += (rs.getObject("websites.time"));
list.add(temp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
public boolean addLY(String a,String b,String c) {//添加记录
//获得三个字符串 添加到 名字 内容 话题 额外添加当前时间
Connection st = getConnection();
PreparedStatement zx;
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
String sql = "insert into websites (name,neirong,huati,time) values(?,?,?,?)";
try {
zx = st.prepareStatement(sql);
zx.setString(1, a);
zx.setString(2, b);
zx.setString(3, c);
zx.setString(4, ((String)df.format(new Date())));
if (zx.execute()) {
return false;
} else
return true;
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public List searchName(String name) {//根据姓名查询相关数据
Connection st = getConnection();
List<String> list = new ArrayList<String>();
String temp = "select id,name,neirong,huati,time from websites where name like ?";
PreparedStatement zx;
try {
zx = st.prepareStatement(temp);
zx.setString(1, "%" + name + "%");
ResultSet rt = zx.executeQuery();
while (rt.next()) {
String qm = rt.getString("id");
qm += " ";
qm += rt.getString("name");
qm += " ";
qm += rt.getString("neirong");
qm += " ";
qm += rt.getString("huati");
qm += " ";
qm += rt.getString("time");
list.add(qm);
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("共" + list.size() + "条留言");//控制台输出
if(list.size()==0)
list.add("没有您的留言");
return list;
}
public boolean deleteData(int id)//根据资料的id进行删除
{
boolean state = false;
Connection ct = getConnection();
String sql = "delete from websites where id = ?";
try {
PreparedStatement qt = ct.prepareStatement(sql);
qt.setInt(1, id);
if (qt.executeUpdate() != 0)
state = true;
else
state = false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return state;
}
}