tkk0408nari

Size: px

Start display at page:

Download "tkk0408nari"

さゆりたかひ
5 years ago
Views:

1 SQLStatement Class Sql

2 Database SQL Structured Query Language( ) ISO JIS

3 Database sql Perl Java SQL

4 ( ) create table tu_data ( id integer not null, -- id aid integer not null, -- id cdate datetime not null, -- data text not null -- );

5 SQL ( ) id,aid select id,aid,data,cdate from tu_data order by id,aid; id aid select data from tu_data where id=13 and aid=3

6 SQL ( insert into tu_data(id,aid,cdate,data) values ( 1001, 3, 'now', ' '); update tu_data set data=' ', cdate='2004:08:21' where id=1001 and aid=3;

7 SQL ( ) delete tu_data; id=1001 delete tu_data where id=1001;

8 Java import java.sql.*; public class jdbcsample{ public static void main(string args[]) throws Exceptionsion{ Class.forName("org.postgresql.Driver"); Connection conn= DriverManager.getConnection("jdbc:postgresql:k4mempub","www",""); Statement st=conn.createstatement(); String sql="select id,aid,data,cdate from tu_data order by id,aid" ResultSet rs = st.executequery(sql); while(rs.next()){ int id = rs.getint(1); int aid = rs.getint(2); String data = rs.getint(3); Date cdate = rs.getdate(4); System.out.println("id="+id+" aid="+aid+ " data=" + data + " cdate" + cdate); } rs.close(); st.close() conn.close(); } }

9 SQL insert field insert update where 2 String

10 id aid String sql = "select date from tu_data" + " where id=10 and aid=5"; id aid String sql = "select data from tu_data" + " where id=" + id + " and aid=" + aid;

11 String sql = "select id,aid from tu_data" + " where data='" + data + "'" + " and cdate < '" + cdate "'";

12 what's michel? => SQL 'what 's michel?' => Java "'what 's michel?'"

13 ( Util.escape() String sql = "select id,aid from tu_data" + " where data='" + Util.escape(data) + "'" + " and cdate < '" + cdate "'"; SQL

14 SQL Torque) SQL

15 JUnit 3

16 SqlStatement SqlStatement SQL (select,insert,update,delete)

17 SqlStatement public class SqlStatement { private int istatementtype; // select or insert.. } private String stable; // private LinkedList lfield; // private LinkedList lvalue; // private LinkedList lwhere; // where private LinkedList lorderby; // order private boolean bforupdate; // select for update...

18 (select) String sql = "select id,aid from tu_data" + " where data='" + Util.escape(data) + "'" + " and cdate < '" + cdate "'"; SqlStatement sql = new SqlStatement(); sql.select("id,aid").from("tu_data"); sql.where("data=",data); sql.where("cdate<",cdate); ResultSet rs = conn.executequery(sql.tostring());

19 (insert/update) SqlStatement sql = new SqlStatement(); sql.table("tu_data"); sql.set("data",data); sql.set("cdate","now"); if(bnew){ sql.set("id",id); sql.set("aid",aid); sql.insert(); } else { } sql.where("id=",id); sql.where("aid=",aid); sql.update();

20 public SqlStatement set(string name, String value){ lfield.add(name); } lvalue.add(quote(value)); return this; public SqlStatement set(string name, int i){ lfield.add(name); } lvalue.add(integer.tostring(i)); return this;

21 Postgresql cdate insert (id,aid,data,cdate) values (100,2,'foo','now'); Oracle insert(id,aid,data,cdate) values(100,2,'foo', SYSDATE); 2 set_nq(string,string)

22 Timestamp public SqlStatement set_ts(string name,timestamp ts){ lfield.add(name); lvalue.add("to_timestamp(" + ts + ",'YYYY/MM/DD HH24:MI:SS'"); return this; }

23 java java Junit regression test

24 javadoc static Factory 1 SqlStatement sql = SqlStatement.makeSelect().from("tu_data");

1 ex01.sql ex01.sql ; user_id from (select user_id ;) user_id * select select (3+4)7, SIN(PI()/2) ; (1) select < > from < > ; :, user_id user_name

1 ex01.sql ex01.sql ; user_id from (select user_id ;) user_id * select select (3+4)*7, SIN(PI()/2) ; (1) select < > from < > ; :, * user_id user_name SQL mysql mysql ( mush, potato) % mysql -u mush -p mydb Enter password:****** mysql>show tables; usertable mysql> ( ) SQL (Query) : select < > from < > where < >; : create, drop, insert, delete,... ; (