//使用jdbc对MySQL表完成增删改查
//工具 myEclipse 以及mysql数据库
//第一步 建立接口 写上抽象方法
public interface UserDao {
public boolean addUser(User user);
public boolean updateUser(User user);
public boolean deleteUser(User user);
public User findById(User user);
public List<User> findAll();
}
//第二步 建立实体模型 写上set和get方法 以及toString方法
public class User {
private int id;
private String name;
private int age;
private String sex;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="
+ sex + ", address=" + address + "]";
}
}
//第三部 写上工具类 连接以及关闭数据库方法
public class BDao {
private Connection con;
public Connection getCon()
{
try {
Class.forName("com.mysql.jdbc.Driver");//加载MySQL连接驱动
con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public void closeCon(Connection con,PreparedStatement psmt,ResultSet rs)
{
try {
if(rs!=null)
{
rs.close();
}
if(psmt!=null)
{
psmt.close();
}
if(con!=null)
{
con.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
//public static void main(String[] args) {
// System.out.println(new BDao().getCon());//如果打印出地址则代表已连接数据库
//}
}
//第四步 实现接口
public class UserDaoImp implements UserDao{
private Connection con=null;
private PreparedStatement psmt=null;
private ResultSet rs=null;
@Override
public boolean addUser(User user) {
BDao bd=new BDao();
con=bd.getCon();
// System.out.println(con);
boolean flag=false;
try {
psmt=con.prepareStatement("insert into users(id,name,age,sex,address) values(?,?,?,?,?)");
psmt.setInt(1, user.getId());
psmt.setString(2, user.getName());
psmt.setInt(3, user.getAge());
psmt.setString(4, user.getSex());
psmt.setString(5, user.getAddress());
int num=psmt.executeUpdate();
if(num>0)
{
flag=true;
}
} catch (Exception e) {
// TODO: handle exception
}finally
{
bd.closeCon(con,psmt,rs);
}
return flag;
}
@Override
public boolean updateUser(User user) {
BDao bd=new BDao();
con=bd.getCon();
boolean flag=false;
try {
psmt=con.prepareStatement("update users set name=?,age=?,sex=?,address=? where id=?");
psmt.setString(1, user.getName());
psmt.setInt(2, user.getAge());
psmt.setString(3, user.getSex());
psmt.setString(4, user.getAddress());
psmt.setInt(5, user.getId());
int num=psmt.executeUpdate();
if(num>0)
{
flag=true;
}
} catch (Exception e) {
// TODO: handle exception
}finally
{
bd.closeCon(con,psmt,rs);
}
return flag;
}
@Override
public boolean deleteUser(User user) {
BDao bd=new BDao();
con=bd.getCon();
boolean flag=false;
try {
psmt=con.prepareStatement("delete from users where id=?");
psmt.setInt(1, user.getId());
int num=psmt.executeUpdate();
if(num>0)
{
flag=true;
}
} catch (Exception e) {
// TODO: handle exception
}finally
{
bd.closeCon(con,psmt,rs);
}
return flag;
}
@Override
public User findById(User user) {
BDao bd=new BDao();
con=bd.getCon();
try {
psmt=con.prepareStatement("select*from users where id=?");
psmt.setInt(1, user.getId());
rs=psmt.executeQuery();
System.out.println(rs.next());
if(rs.next())
{
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getString("sex"));
user.setAddress(rs.getString("address"));
}
System.out.println(user);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
bd.closeCon(con,psmt,rs);
}
return user;
}
@Override
public List<User> findAll() {
List<User> list=new ArrayList<User>();
BDao bd=new BDao();
User user=null;
try {
con=bd.getCon();
String sql="select * from users";
psmt=con.prepareStatement(sql);
rs=psmt.executeQuery();
//System.out.println(psmt);
while(rs.next())
{
user=new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setSex(rs.getString("sex"));
user.setAddress(rs.getString("address"));
list.add(user);
}
} catch (Exception e) {
// TODO: handle exception
}finally
{
bd.closeCon(con,psmt,rs);
}
return list;
}
}
//第五步 测试
public class UserTest {
public static void main(String[] args) throws Exception {
User user=new User();
UserDaoImp ud=new UserDaoImp();
while(true){
System.out.println("欢迎来到用户信息管理系统!");
System.out.println("请选择您要的操作:1.增加信息2修改信息3删除信息4查询单个信息5查询所以信息");
Scanner sc=new Scanner(System.in);
int a=sc.nextInt();
switch(a)
{
case 1:
System.out.println("请输入姓名:");
user.setName(sc.next());
System.out.println("请输入年龄:");
user.setAge(sc.nextInt());
System.out.println("请输入性别:");
user.setSex(sc.next());
System.out.println("请输入地址:");
user.setAddress(sc.next());
ud.addUser(user);
break;
case 2:
System.out.println("请输入要修改的编号:");
user.setId(sc.nextInt());
System.out.println("请输入修改后的姓名:");
user.setName(sc.next());
System.out.println("请输入修改后的年龄:");
user.setAge(sc.nextInt());
System.out.println("请输入修改后的性别:");
user.setSex(sc.next());
System.out.println("请输入修改后的地址:");
user.setAddress(sc.next());
ud.updateUser(user);
break;
case 3:
System.out.println("请输入要删除的编号:");
user.setId(sc.nextInt());
ud.deleteUser(user);
break;
case 4:
System.out.println("请输入要查询的编号:");
int b=sc.nextInt();
user.setId(b);
ud.findById(user);
break;
case 5:
List list=ud.findAll();
Iterator it=list.iterator();
while(it.hasNext())
{
System.out.println(it.next());
}
break;
default:
break;
}
}
}
}
搞定!
使用jdbc对MySQL表完成增删改查
最新推荐文章于 2021-03-16 18:23:24 发布