JDBC02

JDBC02

package com.services;
//链接对象
import java.sql.Connection;
//驱动管理器
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class AB01Services {
    /**
     * 以最原始的方式完成数据添加        
     */
    public boolean addAb01()throws Exception{
        //1.定义驱动串：驱动jar的核心类
        String drivers = "oracle.jdbc.driver.OracleDriver";
        //2.定义链接串
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        //3.加载驱动
        Class.forName(drivers);
        //4.创建链接
        Connection conn = 
                DriverManager.getConnection(url, "admin", "admin1234");
        System.out.println(conn);
        //5.定义sql:在没有保证SQL能正确执行的情况下，不要将SQL写入Java。
        StringBuilder sql = new StringBuilder()
                .append("INSERT INTO AB01(AAB101,AAB102,AAB103,AAB104,AAB105,")
                .append("   AAB106,AAB107,AAB108,AAB109,AAB110,")
                .append("   AAB111,AAB112,AAB113,AAB114,AAB115,")
                .append("   AAB116,AAB117,AAB118,AAB119,AAB120)")
                .append("   VALUES(S_AB01.NEXTVAL,?,?,?,?,")
                .append("   TO_DATE(?,'YYYY-MM-DD'),?,?,?,?,")
                .append("   ?,?,?,?,?,")
                .append("   ?,?,SYSDATE,?,?)")
                ;

        //6.编译sql
        PreparedStatement pstm = conn.prepareStatement(sql.toString());
        //7.参数赋值

        pstm.setObject(1, "2001");
        pstm.setObject(2, "小明");
        pstm.setObject(3, "555555");
        pstm.setObject(4, "1");
        //1
        pstm.setObject(5, "2014-01-01");
        pstm.setObject(6, "11");
        pstm.setObject(7, "济宁");
        pstm.setObject(8, "山东");
        pstm.setObject(9, "丫丫");
        //2
        pstm.setObject(10, "155345678");
        pstm.setObject(11, "02");
        pstm.setObject(12, "软件工程");
        pstm.setObject(13, "加州大学");
        pstm.setObject(14, "02");
        //3
        pstm.setObject(15, "华盛顿大学");
        pstm.setObject(16, "01");
        pstm.setObject(17, "50");
        pstm.setObject(18, "01");


        /**
         * 8.执行sql：
         * 
         * int tag表示SQL语句执行完毕以后影响的记录数
         */

        int tag = pstm.executeUpdate();

        return tag>0;

    }

}

java.sql.SQLException: 无效的列索引
“无效的列索引”其实是个低级的错误，原因无非几个：

1、sql串的?号数目和提供的变量数目不一致：
例如：jdbcTemplate.update(sql, new Object[] {newState,oldState});
如果sql里面有1个?号，Object[]送了2个，就会报错。

2、sql串里的?号书写不正确
英文?和中文？有时难以区分。

3、sql串的?号用''括了起来。
例如：sql="UPDATE abc SET abc.name='?' WHERE abc.id='?'";
把''去掉就可以了。

4，遇到这种情况select*from user where   info   like   %?%;  
虽然这是sql的写法，但是在jdbc 中需要改成 select*from user where   info   like   ?;
如ps.setString(“%”+value+“%”);

---

package text;
import com.services.AB01Services;;
public class AB01ServicesText {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        try {
            AB01ServicesText.addAb01Text();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    private static void addAb01Text()throws Exception {
        AB01Services services = new AB01Services();
        services.addAb01();
    }

}