jdbc的增删改查

连接数据库Tool类

package com.yun.user.tool;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class JdbcUtil {
        private static String url="jdbc:mysql://localhost:3306/mvc";
        private  static String user="root";
        private  static String password="root";
        private static Statement stmt = null;
        private static Connection conn = null;
        private static ResultSet rs;
        public static Connection getConnection() {
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    conn=(Connection) DriverManager.getConnection(url, user, password);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

                return conn;
        }

        public static void closeConnection(Statement stmt,Connection conn){
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        public static void closeConnection(Statement stmt,Connection conn,ResultSet rs){
            if(stmt!=null&&conn!=null){
                closeConnection(stmt,conn);
            }
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

        }

}

查询

public void test1(){
        Connection conn = null;
        Statement stmt = null;
        try{
            //获取连接
            conn = JdbcUtil.getConnection();
            //创建Statement
            stmt = conn.createStatement();
            //准备sql
            String sql = "SELECT * FROM student";
            //执行sql
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+","+name+","+gender);
            }

        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            JdbcUtil.close(conn, stmt);
        }

}

增加（用PreparedStatement 的话插入参数是从1开始的）

private Connection conn;
        private PreparedStatement stam;
        public   void add(User user){
            conn=JdbcUtil.getConnection();
            try {
                String sql="insert into user(name,age,birthday) values(?,?,?)";
                stam = (PreparedStatement) conn.prepareStatement(sql);
                stam.setString(1, user.getUsername());
                stam.setInt(2, user.getAge());
                stam.setDate(3, new Date(user.getHiredate().getTime()));
                int line = stam.executeUpdate();
                System.out.println(user.getHiredate());
                System.out.println("影响了"+line+"行");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                JdbcUtil.closeConnection(stam, conn);
            }
}

删除

public void delete(int id){
                conn=JdbcUtil.getConnection();
                String sql="delete from user where id= ?";
                try {
                    stam = (PreparedStatement) conn.prepareStatement(sql);
                    stam.setInt(1, id);
                    int line = stam.executeUpdate();
                    System.out.println("影响了"+line+"行");
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally{
                    JdbcUtil.closeConnection(stam, conn);
                }
}

通过id查找

public User findUserById(int id){
                conn=JdbcUtil.getConnection();
                String sql="select * from user  where id= ?";
                try {
                    stam = (PreparedStatement) conn.prepareStatement(sql);
                    stam.setInt(1, id);
                    rs = stam.executeQuery();
                    while(rs.next()){
                        String name=rs.getString("name");
                        int age=rs.getInt("age");                   
                        java.util.Date date =new java.util.Date(rs.getDate("birthday").getTime());
                        user =new User(id,name,age,date);
                        System.out.println(user);
                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally{
                    JdbcUtil.closeConnection(stam, conn,rs);
                }

                return user;
}

//更新

public void update(User user){
                conn=JdbcUtil.getConnection();
                String sql="update user set name=?,age=? ,birthday=? where id=? ";
                try {
                    stam = (PreparedStatement) conn.prepareStatement(sql);
                    stam.setString(1, user.getUsername());
                    stam.setInt(2, user.getAge());
                    stam.setDate(3, new Date(user.getHiredate().getTime()));
                    stam.setInt(4, user.getId());
                    int line = stam.executeUpdate();
                    System.out.println("影响了"+line+"行");
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally{
                    JdbcUtil.closeConnection(stam, conn);
                }
}