JAVA之数据库（四）

今天，小狼介绍下JDBC Statement:Statement提供了一个操作数据库SQL语句的功能，可通过它来创建表，插入记录，修改记录，删除记录等操作。来看个栗子:

package com.amaker.test;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import com.maker.util.DBUtil;public class StatementTest {    public static void main(String[] args){        //getStatement();        //createTbl();        //add();        //del();        //update();        query();    }    static void update(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        try {            String sql = "update StuTbl set name = 'bigtom' where id=1";            Statement stmt = conn.createStatement();            stmt.executeUpdate(sql);        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }finally{            util.closeConnection(conn);        }    }    static void del(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        try {            String sql = "delete from StuTbl where id=1";            Statement stmt = conn.createStatement();            stmt.executeUpdate(sql);        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }finally{            util.closeConnection(conn);        }    }    static void add(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        try {            String sql = "insert into StuTbl(id,name,age)values(1,'tom',20)";            Statement stmt = conn.createStatement();            stmt.executeUpdate(sql);        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }finally{            util.closeConnection(conn);        }    }    static void createTbl(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        try {            Statement stmt = conn.createStatement();            String sql = "create table StuTbl(id int,name varchar(20),age int)";            stmt.execute(sql);            //System.out.println(stmt);        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }finally{            util.closeConnection(conn);        }    }    static void getStatement(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        try {            Statement stmt = conn.createStatement();            System.out.println(stmt);        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }    }    static void query(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        try {            String sql = "select id,name,age from StuTbl";            Statement stmt = conn.createStatement();            ResultSet rs = stmt.executeQuery(sql);            while(rs.next()){                int id = rs.getInt(1);                String name = rs.getString("name");                int age = rs.getInt(3);                System.out.println(id+":"+name+":"+age);            }        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }finally{            util.closeConnection(conn);        }    }}

具体效果图小狼就不贴了，大家自己试一下吧。

下面是段内容的更新代码：

package com.amaker.test;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import com.maker.util.DBUtil;public class ResultSetTest {    public static void main(String[] args) {        //testResultSet();        //List list = listStu();        //System.out.println(list);        updatableResultSet();    }    static void updatableResultSet(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        String sql = "select id,name,age from StuTbl";        Statement stmt;        try {            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                    ResultSet.CONCUR_UPDATABLE);            ResultSet rs = stmt.executeQuery(sql);            rs.absolute(2);            rs.updateString("name","bigbigbigtom");            rs.updateRow();        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }    }    static List listStu(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        String sql = "select id,name,age from StuTbl";        Statement stmt;        try {            stmt = conn.createStatement();            ResultSet rs = stmt.executeQuery(sql);            List list = new ArrayList();            while(rs.next()){                int id = rs.getInt(1);                String name = rs.getString(2);                int age = rs.getInt(3);                Stu s = new Stu();                s.setAge(age);                s.setId(id);                s.setName(name);                list.add(s);            }            return list;        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }        return null;    }      static void testResultSet(){        DBUtil util = new DBUtil();        Connection conn = util.openConnection();        String sql = "select id,name,age from StuTbl";        Statement stmt;        try {            stmt = conn.createStatement();            ResultSet rs = stmt.executeQuery(sql);            rs.next();            int row = rs.getRow();            System.out.println(row);            boolean b = rs.isBeforeFirst();            System.out.println(b);            /*int row = rs.getRow();            System.out.println(row);            boolean b = rs.isBeforeFirst();            System.out.println(b);*/        } catch (SQLException e) {            // TODO: handle exception            e.printStackTrace();        }    }}class Stu{    private int id;    private String name;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    private int age;}

。。出了点小错

com.mysql.jdbc.NotUpdatable: Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.    at com.mysql.jdbc.UpdatableResultSet.generateStatements(UpdatableResultSet.java:589)    at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1493)    at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:2450)    at com.mysql.jdbc.UpdatableResultSet.updateString(UpdatableResultSet.java:2488)    at com.amaker.test.ResultSetTest.updatableResultSet(ResultSetTest.java:33)    at com.amaker.test.ResultSetTest.main(ResultSetTest.java:19)

原因：未添加主键。
Alter table StuTbl add primary key(id);

先说到这儿吧 See U~~