JDBC之preparedStatement

直接贴代码案例，理论知识可以从隔壁看看。

prestmtest.java

package PREPAREDSTAMENT;

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

import org.junit.Test;

public class prestmtest {
          String url="jdbc:mysql://localhost:3306/data";
          String user="root";
          String password="123456";
          @Test

          /*
           * 增加
          */
          public  void testInsert(){
            Connection conn=null;
            PreparedStatement pstmt=null;
            //获取链接对象
           try {
            Class.forName("com.mysql.jdbc.Driver");
             conn=DriverManager.getConnection(url, user, password);
                //准备预编译sql语句
             String sql="insert into xxb values(?,?,?)";
             //执行sql
             pstmt=conn.prepareStatement(sql);
             //设置参数


            pstmt.setInt(1, 4);
             pstmt.setString(2, "杨六");
             pstmt.setString(3, "女");
             int count=pstmt.executeUpdate();
             System.out.print("影响了"+count+"行");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        } //throw new RuntimeException(e);
           finally{
             if(pstmt!=null)
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
             if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
           }
          }
          /*
           * 删除
          */
    public  void testDelete(){
        Connection conn=null;
        PreparedStatement pstmt=null;
        //获取链接对象
     try {
        Class.forName("com.mysql.jdbc.Driver");
         conn=DriverManager.getConnection(url, user, password);
            //准备预编译sql语句
         String sql="delete from xxb where sid=?";
         //执行sql
         pstmt=conn.prepareStatement(sql);
         //设置参数
         pstmt.setInt(1, 4);
         int count=pstmt.executeUpdate();
         System.out.print("影响了"+count+"行");
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        throw new RuntimeException(e);
    } //throw new RuntimeException(e);
     finally{
         if(pstmt!=null)
            try {
                pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException(e);
            }
         if(conn!=null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException(e);
            }
     }
          }

          /*
           * 修改
          */
          public  void testUpdate(){
            Connection conn=null;
            PreparedStatement pstmt=null;
            //获取链接对象
           try {
            Class.forName("com.mysql.jdbc.Driver");
             conn=DriverManager.getConnection(url, user, password);
                //准备预编译sql语句
             String sql="update xxb set sname=? where sid=?";
             //执行sql
             pstmt=conn.prepareStatement(sql);
             //设置参数



             pstmt.setString(1, "张三");
             pstmt.setInt(2, 1);
             int count=pstmt.executeUpdate();
             System.out.print("影响了"+count+"行");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        } //throw new RuntimeException(e);
           finally{
             if(pstmt!=null)
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
             if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
           }
                }  
          /*
           * 查询
         */
          public  void testQuery(){
            Connection conn=null;
            PreparedStatement pstmt=null;
            //获取链接对象
           try {
            Class.forName("com.mysql.jdbc.Driver");
             conn=DriverManager.getConnection(url, user, password);
                //准备预编译sql语句
             String sql="select *from xxb";
             //执行sql
             pstmt=conn.prepareStatement(sql);
             //遍历
             ResultSet rs=pstmt.executeQuery();
             while(rs.next()){
                 int id=rs.getInt("sid");
                 String name=rs.getString("sname");
                 String gender=rs.getString("sgender");
                 System.out.println(id+" "+name+" "+gender);
             }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        } //throw new RuntimeException(e);
           finally{
             if(pstmt!=null)
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
             if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
           }
                }     
}

数据库文件

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
--  Table structure for `xxb`
-- ----------------------------
DROP TABLE IF EXISTS `xxb`;
CREATE TABLE `xxb` (
  `sid` int(11) default NULL,
  `sname` varchar(20) default NULL,
  `sgender` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records 
-- ----------------------------
INSERT INTO `xxb` VALUES ('1','zhangsan','man'), ('2','lisi','woman'), ('3','wangwu','man');