timestamptest.java

Java写的TDS协议(JDBC/ODBC)实现

package net.sourceforge.jtds.test;

import java.sql.*;
import java.math.BigDecimal;
import net.sourceforge.jtds.jdbc.Tds;
import net.sourceforge.jtds.util.Logger;
import junit.framework.TestCase;
import junit.framework.TestSuite;
import junit.framework.AssertionFailedError;

/**
 * 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
    {
        Connection cx = getConnection();

        dropTable("#t0000");

        Statement stmt = cx.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);
        }
        assertTrue(count == rowsToAdd);

        PreparedStatement pStmt = cx.prepareStatement("select i from #t0000 where i = ?");

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

        assertTrue("Expected a result set", rs.next());
        assertTrue(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());
        assertTrue(rs.getLong(1) == 8);
        assertTrue("Expected no result set", !rs.next());
    }

    public void testTimestamps0001() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0001");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0001             " +
            "  (t1 datetime not null,       " +
            "   t2 datetime null,           " +
            "   t3 smalldatetime not null,  " +
            "   t4 smalldatetime null)");

        PreparedStatement pStmt = cx.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 = cx.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));
    }

    public void testTimestamps0004() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0004");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0004 "
            + "  (mytime  datetime not null, "
            + "   mytime2 datetime null,     "
            + "   mytime3 datetime null     )");

        PreparedStatement pStmt = cx.prepareStatement(
            "insert into #t0004 values ('1998-09-09 15:35:05', ?, ?)");

        Timestamp   t0 = Timestamp.valueOf("1998-09-09 15:35:05");
        pStmt.setTimestamp(1, t0);
        pStmt.setTimestamp(2, t0);
        int count = pStmt.executeUpdate();

        pStmt.setNull(2, java.sql.Types.TIMESTAMP);
        count = pStmt.executeUpdate();
        pStmt.close();

        pStmt = cx.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(t1, t2);
        assertEquals(t1, t3);
        assertEquals(t2, t3);

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

    public void testEscape(String sql, String expected) throws Exception
    {
        String tmp = Tds.toNativeSql(sql, Tds.SQLSERVER);
        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 '\\%%' ");
        testEscape("select * from tmp where a like 'bbb' {escape 'b'}",
            "select * from tmp where a like 'bbb' ");
        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
    {
        Connection cx = getConnection();

        dropTable("#t0007");

        Statement stmt = cx.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);
        }
        assertTrue(count == rowsToAdd);

        PreparedStatement pStmt = cx.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());
    }

    public void testPreparedStatement0008() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0008");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0008              "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        PreparedStatement pStmt = cx.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();
        }
        assertTrue(count == rowsToAdd);
        pStmt.close();

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

        count = 0;
        while (rs.next())
        {
            count++;
            assertTrue(rs.getString(1).trim().length() == rs.getInt(2));
        }
        assertTrue(count == rowsToAdd);
    }

    public void testPreparedStatement0009() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0009");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0009 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        cx.setAutoCommit(false);

        PreparedStatement pStmt = cx.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();
        assertTrue(count == rowsToAdd);
        cx.rollback();

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

        count = 0;
        while (rs.next())
        {
            count++;
            assertTrue(rs.getString(1).trim().length() == rs.getInt(2));
        }
        assertTrue(count == 0);
        cx.commit();
        stmt.close();

        pStmt = cx.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();
        }
        assertTrue(count == rowsToAdd);
        cx.commit();
        pStmt.close();

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

        count = 0;
        while (rs.next())
        {
            count++;
            assertTrue(rs.getString(1).trim().length() == rs.getInt(2));
        }
        assertTrue(count == rowsToAdd);
        cx.commit();
        stmt.close();
        cx.setAutoCommit(true);
    }

    public void testTransactions0010() throws Exception
    {
        Connection cx = getConnection();

        dropTable("#t0010");

        Statement stmt = cx.createStatement();
        stmt.executeUpdate("create table #t0010 "
            + "  (i  integer  not null,      "
            + "   s  char(10) not null)      ");

        cx.setAutoCommit(false);

        PreparedStatement pStmt = cx.prepareStatement(
            "insert into #t0010 values (?, ?)");