timestamptest.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

package net.sourceforge.jtds.test;

import java.math.BigDecimal;
import java.sql.*;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.GregorianCalendar;

import net.sourceforge.jtds.jdbc.Driver;
import net.sourceforge.jtds.util.Logger;

import junit.framework.TestSuite;

/**
 * test getting timestamps from the database.
 */
public class TimestampTest extends DatabaseTestCase {
    public TimestampTest(String name) {
        super(name);
    }

    public static void main(String args[]) {
        boolean loggerActive = args.length > 0;
        Logger.setActive(loggerActive);

        if (args.length > 0) {
            junit.framework.TestSuite s = new TestSuite();
            for (int i = 0; i < args.length; i++) {
              s.addTest(new TimestampTest(args[i]));
            }
            junit.textui.TestRunner.run(s);
        } else {
            junit.textui.TestRunner.run(TimestampTest.class);
        }

        // new TimestampTest("test").testOutputParams();
    }

    public void testBigint0000() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0000 "
            + "  (i  decimal(28,10) not null, "
            + "   s  char(10) not null) ");

        final int rowsToAdd = 20;
        int count = 0;

        for (int i = 1; i <= rowsToAdd; i++) {
            String sql = "insert into #t0000 values (" + i + ", 'row" + i + "')";
            count += stmt.executeUpdate(sql);
        }

        stmt.close();
        assertEquals(count, rowsToAdd);

        PreparedStatement pstmt = con.prepareStatement("select i from #t0000 where i = ?");

        pstmt.setLong(1, 7);
        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getLong(1), 7);
        assertTrue("Expected no result set", !rs.next());

        pstmt.setLong(1, 8);
        rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getLong(1), 8);
        assertTrue("Expected no result set", !rs.next());

        pstmt.close();
    }

    public void testTimestamps0001() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0001             "
                           + "  (t1 datetime not null,       "
                           + "   t2 datetime null,           "
                           + "   t3 smalldatetime not null,  "
                           + "   t4 smalldatetime null)");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement(
            "insert into #t0001 values (?, '1998-03-09 15:35:06.4',        "
            + "                         ?, '1998-03-09 15:35:00')");
        Timestamp   t0 = Timestamp.valueOf("1998-03-09 15:35:06.4");
        Timestamp   t1 = Timestamp.valueOf("1998-03-09 15:35:00");

        pstmt.setTimestamp(1, t0);
        pstmt.setTimestamp(2, t1);
        int count = pstmt.executeUpdate();
        assertTrue(count == 1);
        pstmt.close();

        pstmt = con.prepareStatement("select t1, t2, t3, t4 from #t0001");

        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());

        assertEquals(t0, rs.getTimestamp(1));
        assertEquals(t0, rs.getTimestamp(2));
        assertEquals(t1, rs.getTimestamp(3));
        assertEquals(t1, rs.getTimestamp(4));

        pstmt.close();
    }

    public void testTimestamps0004() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0004 "
            + "  (mytime  datetime not null, "
            + "   mytime2 datetime null,     "
            + "   mytime3 datetime null     )");
        stmt.close();

        PreparedStatement pstmt = con.prepareStatement(
            "insert into #t0004 values ('1964-02-14 10:00:00.0', ?, ?)");

        Timestamp   t0 = Timestamp.valueOf("1964-02-14 10:00:00.0");
        pstmt.setTimestamp(1, t0);
        pstmt.setTimestamp(2, t0);
        assertEquals(1, pstmt.executeUpdate());

        pstmt.setNull(2, java.sql.Types.TIMESTAMP);
        assertEquals(1, pstmt.executeUpdate());
        pstmt.close();

        pstmt = con.prepareStatement("select mytime, mytime2, mytime3 from #t0004");

        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);
        Timestamp t1, t2, t3;

        assertTrue("Expected a result set", rs.next());
        t1 = rs.getTimestamp(1);
        t2 = rs.getTimestamp(2);
        t3 = rs.getTimestamp(3);
        assertEquals(t0, t1);
        assertEquals(t0, t2);
        assertEquals(t0, t3);

        assertTrue("Expected a result set", rs.next());
        t1 = rs.getTimestamp(1);
        t2 = rs.getTimestamp(2);
        t3 = rs.getTimestamp(3);
        assertEquals(t0, t1);
        assertEquals(t0, t2);
        assertEquals(null, t3);

        pstmt.close();
    }

    public void testEscape(String sql, String expected) throws Exception {
        String tmp = con.nativeSQL(sql);

        assertEquals(tmp, expected);
    }

    public void testEscapes0006() throws Exception {
        testEscape("select * from tmp where d={d 1999-09-19}",
            "select * from tmp where d='19990919'");
        testEscape("select * from tmp where d={d '1999-09-19'}",
            "select * from tmp where d='19990919'");
        testEscape("select * from tmp where t={t 12:34:00}",
            "select * from tmp where t='12:34:00'");
        testEscape("select * from tmp where ts={ts 1998-12-15 12:34:00.1234}",
            "select * from tmp where ts='19981215 12:34:00.123'");
        testEscape("select * from tmp where ts={ts 1998-12-15 12:34:00}",
            "select * from tmp where ts='19981215 12:34:00.000'");
        testEscape("select * from tmp where ts={ts 1998-12-15 12:34:00.1}",
            "select * from tmp where ts='19981215 12:34:00.100'");
        testEscape("select * from tmp where ts={ts 1998-12-15 12:34:00}",
            "select * from tmp where ts='19981215 12:34:00.000'");
        testEscape("select * from tmp where d={d 1999-09-19}",
            "select * from tmp where d='19990919'");
        testEscape("select * from tmp where a like '\\%%'",
            "select * from tmp where a like '\\%%'");
        testEscape("select * from tmp where a like 'b%%' {escape 'b'}",
            "select * from tmp where a like 'b%%' escape 'b'");
        testEscape("select * from tmp where a like 'bbb' {escape 'b'}",
            "select * from tmp where a like 'bbb' escape 'b'");
        testEscape("select * from tmp where a='{fn user}'",
            "select * from tmp where a='{fn user}'");
        testEscape("select * from tmp where a={fn user()}",
            "select * from tmp where a=user_name()");
    }

    public void testPreparedStatement0007() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0007 "
            + "  (i  integer  not null, "
            + "   s  char(10) not null) ");

        final int rowsToAdd = 20;
        int count = 0;

        for (int i = 1; i <= rowsToAdd; i++) {
            String sql = "insert into #t0007 values (" + i + ", 'row" + i + "')";

            count += stmt.executeUpdate(sql);
        }

        stmt.close();
        assertEquals(count, rowsToAdd);

        PreparedStatement pstmt = con.prepareStatement("select s from #t0007 where i = ?");

        pstmt.setInt(1, 7);
        ResultSet rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getString(1).trim(), "row7");
        // assertTrue("Expected no result set", !rs.next());

        pstmt.setInt(1, 8);
        rs = pstmt.executeQuery();
        assertNotNull(rs);

        assertTrue("Expected a result set", rs.next());
        assertEquals(rs.getString(1).trim(), "row8");
        assertTrue("Expected no result set", !rs.next());

        pstmt.close();
    }

    public void testPreparedStatement0008() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0008              "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        PreparedStatement pstmt = con.prepareStatement(
            "insert into #t0008 values (?, ?)");

        final int rowsToAdd = 8;
        final String theString = "abcdefghijklmnopqrstuvwxyz";
        int count = 0;

        for (int i = 1; i <= rowsToAdd; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, theString.substring(0, i));

            count += pstmt.executeUpdate();
        }

        assertEquals(count, rowsToAdd);
        pstmt.close();

        ResultSet rs = stmt.executeQuery("select s, i from #t0008");
        assertNotNull(rs);

        count = 0;

        while (rs.next()) {
            count++;
            assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
        }
        assertTrue(count == rowsToAdd);
        stmt.close();
        pstmt.close();
    }

    public void testPreparedStatement0009() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0009 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        con.setAutoCommit(false);

        PreparedStatement pstmt = con.prepareStatement(
            "insert into #t0009 values (?, ?)");

        int rowsToAdd = 8;
        final String theString = "abcdefghijklmnopqrstuvwxyz";
        int count = 0;

        for (int i = 1; i <= rowsToAdd; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, theString.substring(0, i));

            count += pstmt.executeUpdate();
        }

        pstmt.close();
        assertEquals(count, rowsToAdd);
        con.rollback();

        ResultSet rs = stmt.executeQuery("select s, i from #t0009");
        assertNotNull(rs);

        count = 0;

        while (rs.next()) {
            count++;
            assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
        }

        assertEquals(count, 0);
        con.commit();

        pstmt = con.prepareStatement("insert into #t0009 values (?, ?)");
        rowsToAdd = 6;
        count = 0;

        for (int i = 1; i <= rowsToAdd; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, theString.substring(0, i));

            count += pstmt.executeUpdate();
        }

        assertEquals(count, rowsToAdd);
        con.commit();
        pstmt.close();

        rs = stmt.executeQuery("select s, i from #t0009");

        count = 0;

        while (rs.next()) {
            count++;
            assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
        }

        assertEquals(count, rowsToAdd);
        con.commit();
        stmt.close();
        con.setAutoCommit(true);
    }

    public void testTransactions0010() throws Exception {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("create table #t0010 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        con.setAutoCommit(false);

        PreparedStatement pstmt = con.prepareStatement(
            "insert into #t0010 values (?, ?)");

        int rowsToAdd = 8;
        final String theString = "abcdefghijklmnopqrstuvwxyz";
        int count = 0;

        for (int i = 1; i <= rowsToAdd; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, theString.substring(0, i));

            count += pstmt.executeUpdate();
        }

        assertEquals(count, rowsToAdd);
        con.rollback();

        ResultSet rs = stmt.executeQuery("select s, i from #t0010");
        assertNotNull(rs);

        count = 0;