SQL LESSON 2

用java 访问数据库

Java DataBase Connection

Class.forName(""); //驱动
Connection con=DriverManager.getConnection("","","");
//引号里分别是url、名字、密码（初始密码是空）
Statement st=con.createStatement('','','');
ResultSet rs=st.executeQuery(); //select
            =st.executeUpdate();//delete/update/insert

Result Set:
next()
把指针往下移动一行

sql里字符串要用单引号‘’

Transaction 事务

"update T set a=a+100 where id=2;"
"update T set a=a-100 where id =1"

连接池：可更换的ResultSet

java---jdbc---.jar----DB

上课代码案例

package database;
import java.io.FileInputStream;
import java.sql.*;
public class Test {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/temp", "root", "");
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery("select * from test where id>5");
            rs.last();
            int rowCount=rs.getRow();
            rs.first();
            rs.absolute(3);
            rs.previous();
            while(rs.next()){
                String s1=rs.getString(1);
                String s2=rs.getString(2);
                System.out.println(s1+"\t"+s2+"\t" + rs.getString(3)+"\t"+ rs.getString(4));
            }
            //st.executeUpdate("update test set b=b+1 ");
            String ss="123";
            st.executeUpdate("insert into test(a,b,c) values('123','23','45')");
            int i=4000;
//transaction start...
            con.setAutoCommit(false);
            try{
                st.executeUpdate("update test set b=b+"+i+" where id=1");
                st.executeUpdate("update test set b=b-"+i+" where id=2");
                con.commit();
            }
            catch(Exception e){
                con.rollback();
                System.out.println("error,already rollbacked!");
            }
            //end
            con.setAutoCommit(true);
            PreparedStatement pst=con.prepareStatement("insert into test(a,b,c,d) values(?,?,?,?)");
            String a="abcd";
            String c="defg";
            pst.setString(1, a);
            pst.setInt(2, (int)(Math.random()*100));
            pst.setString(3, c);
            pst.setBinaryStream(4, new FileInputStream("20130208224050_QJ8M2.gif"));
            pst.executeUpdate();
//          con.commit();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

在navicat里的视图

运行之前别忘了在系统里打开mySQL!