JDBC的增删改查

 

获取信息:

　　import java.sql.

　　/**

 　　* 获取用户所有信息
 */

public class Insert {
    public static void main(String[] args)throws Exception {
        Insert a = new Insert();
        a.student();
    }

    public  void student()throws Exception{
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null ;
        try {
            //加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/wuyi","root","010510");
            //创建执行sql语句的对象
            stmt = conn.createStatement();
            //创建sql语句
            String sql = "select * from student1";
            //执行sql语句,得到rs指针
            rs=stmt.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt("studentNo")+"\t"+rs.getString("studentName"));
            }
        }catch (ClassCastException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                if (conn!=null){
                    conn.close();
                }
                if (stmt!=null){
                    stmt.close();
                }
                if (rs!=null){
                    rs.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

添加数据
　　　　

import java.sql.*;

/**
 * 新增
 */

public class Add {
    public static void main(String[] args)throws Exception {
        Add a = new Add();
        a.add("123","你爸爸",2 );
    }
    public int add(String loginPwd , String studentName,int id)throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        int count=0;
        try {
            //加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/wuyi","root","010510");
            //创建sql语句
            String sql = "insert into student2(loginPwd,studentName,id) values(?,?,?)";
            //创建sql语句的对象
            stmt= conn.prepareStatement(sql);
            stmt.setString(1,loginPwd);
            stmt.setString(2,studentName);
            stmt.setInt(3,id);
            //执行sql语句
            count=stmt.executeUpdate();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            try {
                if (conn!=null){
                    conn.close();
                }
                if (stmt!=null){
                    stmt.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return count;
    }
}

改数据

import *

/**
 * 改数据
 */


public class Update {
    public static void main(String[] args)throws Exception {
        Update a = new Update();
        a.show(10000,"憨憨");

    }
    public int show(int  studentNo ,String studentName )throws Exception{
        Connection conn=null;
        PreparedStatement stmt = null ;
        int count= 0;
        try {
         //加载驱动类
         Class.forName("com.mysql.jdbc.Driver");
         //创建数据库连接
         conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/wuyi","root","010510");
         //创建sql语句
         stmt=conn.prepareStatement("update student1 set studentName=? where studentNo=?");
         stmt.setString(1,studentName);
         stmt.setInt(2,studentNo);
         //执行sql语句
          count=stmt.executeUpdate();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            if(conn!=null){
                conn.close();
            }
            if (stmt!=null){
                stmt.close();
            }
        }
        return count;
    }
}

删除数据

import *

/**
 * 删除信息
 */

public class Delete {
    public static void main(String[] args)throws Exception {
        Delete a  = new Delete();
        a.delete(2);

    }
    public int delete(int id)throws Exception{
        Connection conn= null;
        PreparedStatement stmt = null;
        int count =0;
        try {
            //加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/wuyi","root","010510");
            //关闭自动提交
            conn.setAutoCommit(false);
            //创建sql语句的对象
            stmt=conn.prepareStatement("delete from student2 where id=?");
            stmt.setInt(1,id);
            //执行sql语句
            count=stmt.executeUpdate();
            //编程式事务
            //手动提交事务
            conn.commit();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
            //手动滚回
            if (conn!=null){
                conn.rollback();
            }
        }finally {
            try {
                if (conn!=null);{
                conn.close();
                }
                if (stmt!=null){
                    stmt.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return count;
    }
}

查询数据
　　

import java.sql.*;

/**
 * 根据ID查询学生
 */
public  class  Select {
    public static void main(String[]args) throws Exception{
        Select a = new Select();
        a.Select1(10000);
        }

    public  void Select1(int id)throws Exception{
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
        //加载驱动器
        Class.forName("com.mysql.jdbc.Driver");
        //创建数据库连接
        conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/wuyi","root","010510");
        //创建sql语句的对象
        stmt=conn.createStatement();
        //创建一条sql语句
        String sql="select * from student1 where studentNo="+id;
        //执行sql语句,得到rs指针
        rs=stmt.executeQuery(sql);
        //通过rs获得单元格
        while(rs.next()){
        System.out.println(rs.getInt("studentNo")+"\t"+rs.getString("studentName"));
        }
        }catch(ClassCastException e){
        e.printStackTrace();
        }catch(SQLException e){
        e.printStackTrace();
        }finally{
        try{
        if(conn!=null){
        conn.close();
        }
        if(stmt!=null){
        stmt.close();
        }
        if(rs!=null){
        rs.close();
        }
        }catch(SQLException e){
        e.printStackTrace();
        }
        }
        }
}