jdbc---增删改查

在与数据库交互的过程中，难以避免的就是程序要对数据库进行增删改查的操作。在这里面都是重复使用java所提供的方法函数进行调用而已，所以学习入门的话还是挺简单的。

这里面对一些公共模块进行了封装处理，因为一些经常使用的东西不用每次需要都写入，这样子的话对效率，美观的有一定的提升。

基本的代码结构图：

公共类：

package com.d.cn.dao.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JdbcUtil {public static Connection conn = null;static{try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static Connection getConnection(){try {conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javatest","root","root");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void closeAll(Connection conn,Statement state,ResultSet rs){if(rs!=null){try {rs.close();} catch (Exception e) {// TODO Auto-generated catch blockSystem.out.print("错误了");e.printStackTrace();}}if(state!=null){try {state.close();} catch (Exception e) {// TODO Auto-generated catch blockSystem.out.print("错误了");e.printStackTrace();}}if(conn!=null){try {conn.close();} catch (Exception e) {// TODO Auto-generated catch blockSystem.out.print("错误了");e.printStackTrace();}}}}

增删改查类：

package com.d.cn.test;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import java.sql.ResultSet;import com.d.cn.dao.entity.User;import com.d.cn.dao.utils.JdbcUtil;public class jdbcCrud {public User getUserMsg(User user){User ResultUser = null;Connection conn = null;ResultSet rs = null;Statement state = null;try {conn = JdbcUtil.getConnection();String sqlAll = "SELECT id ,name , password FROM `user` WHERE name='"+user.getName()+"' AND password='"+user.getPassword()+"'";//用statement接口发送sql语句并执行，执行后的结果集放在ResultSetstate = conn.createStatement();rs = state.executeQuery(sqlAll);//处理结果while (rs.next()) {ResultUser = new User();ResultUser.setId(rs.getInt("id"));ResultUser.setName(rs.getString("name"));ResultUser.setPassword(rs.getString("password"));}} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.print("错误了");e.printStackTrace();}finally{JdbcUtil.closeAll(conn, state, rs);}return ResultUser;}/* * 增 * */public int insert(User user){int row = -1;Connection conn = null;Statement state = null;try {conn = JdbcUtil.getConnection();state = conn.createStatement();String sqlAdd = "INSERT INTO `user` (id,name,password)VALUES ("+user.getId()+",'"+user.getName()+"','"+user.getPassword()+"')";row = state.executeUpdate(sqlAdd);}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JdbcUtil.closeAll(conn, state, null);}return row;}/* * 删 * */public int delete(User user){int row = -1;Connection conn = null;Statement state = null;try {conn = JdbcUtil.getConnection();state = conn.createStatement();String sqlAdd = "DELETE FROM `User` WHERE id = "+user.getId();row = state.executeUpdate(sqlAdd);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JdbcUtil.closeAll(conn, state, null);}return row;}/* * 改 * */public int update(User user){int row = -1;Connection conn = null;Statement state = null;try {conn = JdbcUtil.getConnection();state = conn.createStatement();String sqlUpd = "UPDATE `user` SET name='"+user.getName()+"',password='"+user.getPassword()+"'  WHERE id = "+user.getId();row = state.executeUpdate(sqlUpd);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JdbcUtil.closeAll(conn, state, null);}return row;}/* * 查 * */public List<User> listAll(){List<User> users = null;Connection conn = null;ResultSet rs = null;Statement state = null;try {users = new ArrayList<User>();conn = JdbcUtil.getConnection();String sqlAll = "SELECT id ,name , password FROM `user`";//用statement接口发送sql语句并执行，执行后的结果集放在ResultSetstate = conn.createStatement();rs = state.executeQuery(sqlAll);//处理结果while (rs.next()) {User user = new User();user.setId(rs.getInt("id"));user.setName(rs.getString("name"));user.setPassword(rs.getString("password"));users.add(user);}}catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.print("错误了");e.printStackTrace();}finally{JdbcUtil.closeAll(conn, state, rs);}return users;}}

测试类：

package com.d.cn.test;import java.util.List;import java.util.Scanner;import com.d.cn.dao.entity.User;import com.d.cn.test.jdbcCrud;public class test {public static void main(String[] args) {//查询jdbcCrud jdbcCrud = new jdbcCrud();List<User> users = jdbcCrud.listAll();System.out.println("编号\t姓名\t密码\t");for (User user : users) {System.out.println(user.getId()+"\t"+user.getName()+"\t"+user.getPassword());}//添加数据//jdbcCrud jdbcCrud = new jdbcCrud();//int flag = jdbcCrud.insert(new User(3,"六六","123"));//if(flag==1){//System.out.println("添加数据成功！");//}else{//System.out.println("添加数据失败！");//}////删除数据//jdbcCrud jdbcCrud = new jdbcCrud();//int flag = jdbcCrud.delete(new User(3,"",""));//if(flag==1){//System.out.println("删除数据成功！");//}else{//System.out.println("删除数据失败！");//}////修改数据//jdbcCrud jdbcCrud = new jdbcCrud();//int flag = jdbcCrud.update(new User(0,"张三","333"));//if(flag==1){//System.out.println("修改数据成功！");//}else{//System.out.println("修改数据失败！");//}////修改数据//jdbcCrud jdbcCrud = new jdbcCrud();//Scanner Input = new Scanner(System.in);//System.out.print("请输入登录用户名：");//String name = Input.next();//System.out.print("请输入登录密码：");//String password = Input.next();//User ResultUser = jdbcCrud.getUserMsg(new User(0,name,password));//if(ResultUser!=null){//System.out.println("登录成功！");//}else{//System.out.println("登录失败！");//}}}

就这样一个简单的增删改查的操作就完成了。