mytest.java

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

package net.sourceforge.jtds.tools;

import java.sql.*;
import java.util.*;

/**
 * Title:
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:
 * @author
 * @version 1.0
 */

public class MyTest
{
    public static void main2(String args[]) throws Exception
    {
        System.out.println(new net.sourceforge.jtds.jdbc.Driver().acceptsURL("jdbc:jtds:sqlserver://server:12/db"));
    }

    public static void main3(String args[]) throws Exception
    {
        Class.forName( "net.sourceforge.jtds.jdbc.Driver" );
        String url = "jdbc:jtds:sqlserver://server/Alin_Test";
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "");
        props.setProperty("charset", "Cp1252");
//        props.setProperty("TDS", "4.2");
        net.sourceforge.jtds.util.Logger.setActive(true);
        Connection con = DriverManager.getConnection(url, props);

        ResultSet rs = con.getMetaData().getColumns(null, null, null, null );
        ResultSetMetaData md = rs.getMetaData();

//        for( int i=1; i<=md.getColumnCount(); i++ )
//            System.out.println(i+" "+md.getColumnLabel(i)+" "+md.getColumnName(i));

        while( rs.next() )
        {
            for( int i=1; i<=md.getColumnCount(); i++ )
                System.out.print(rs.getString(i)+"\t");
            System.out.println();
        }

//        System.out.println(con.nativeSQL("select count(*) from CTSTABLE2 where cof_name like '%-%' { escape '\\' }"));

//        Statement s = con.createStatement();
//        ResultSet rs = s.executeQuery("SELECT date FROM datetable");
//
//        while( rs.next() )
//            System.out.println(rs.getString(1));
//        con.createStatement().executeQuery("Select 1");

        con.close();
    }

    public static void main1(String args[]) throws Exception
    {
        Class.forName( "net.sourceforge.jtds.jdbc.Driver" );
        String url = "jdbc:jtds:sqlserver://server:1433/Alin_Test";
//        Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
//        String url = "jdbc:odbc:Alin_Test";
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "");
        props.setProperty("charset", "Cp1252");
        props.setProperty("TDS", "7.0");
        net.sourceforge.jtds.util.Logger.setActive(true);
        Connection con = DriverManager.getConnection(url, props);

//        ResultSet rs = con.createStatement().executeQuery("Select Top 1 * From TypeTest");
//        ResultSetMetaData rsm = rs.getMetaData();
//
//        con.setCatalog("printscout");
//
//        System.out.println("<table>");
//        System.out.println("<tr><td>ColumnName</td><td>ColumnLabel</td><td>CatalogName"
//            +"</td><td>SchemaName</td><td>TableName</td><td>ColumnDisplaySize"
//            +"</td><td>ColumnType</td><td>getColumnTypeName</td><td>Precision"
//            +"</td><td>Scale</td><td>isAutoIncrement</td><td>isCaseSensitive"
//            +"</td><td>isCurrency</td><td>isDefinitelyWritable</td><td>isNullable"
//            +"</td><td>isReadOnly</td><td>isSearchable</td><td>isSigned"
//            +"</td><td>isWritable</td></tr>");
//        for( int i=1; i<=rsm.getColumnCount(); i++ )
//        {
//            System.out.println("<tr><td>"+rsm.getColumnName(i)+"</td><td>"+rsm.getColumnLabel(i)+"</td><td>"+rsm.getCatalogName(i)
//                +"</td><td>"+rsm.getSchemaName(i)+"</td><td>"+rsm.getTableName(i)+"</td><td>"+rsm.getColumnDisplaySize(i)
//                +"</td><td>"+rsm.getColumnType(i)+"</td><td>"+rsm.getColumnTypeName(i)+"</td><td>"+rsm.getPrecision(i)
//                +"</td><td>"+rsm.getScale(i)+"</td><td>"+rsm.isAutoIncrement(i)+"</td><td>"+rsm.isCaseSensitive(i)
//                +"</td><td>"+rsm.isCurrency(i)+"</td><td>"+rsm.isDefinitelyWritable(i)+"</td><td>"+rsm.isNullable(i)
//                +"</td><td>"+rsm.isReadOnly(i)+"</td><td>"+rsm.isSearchable(i)+"</td><td>"+rsm.isSigned(i)
//                +"</td><td>"+rsm.isWritable(i)+"</td></tr>");
//        }
//        System.out.println("</table>");
//        con.close();

        try {
          con.setAutoCommit(false);
          con.createStatement().execute("SET NOCOUNT ON");
          String s =
              "update \ndbo.secondtable set\n text = ? "+
              "where\n firsttableid = 1";
//              "insert into ODYSEA.FTD_ZADAVATELE (ZADAVATEL, SPRAVCE, POSL_PRIJEMCE, "+
//              "POSL_VAR_SLM, POSL_CIS_SLM, POSL_SPEC_SLM) values (?, 0, 3, 3, 3, 3)";
          PreparedStatement ps = con.prepareStatement(s);
          try {
//        ps.setInt(1, 334);
            ps.setString(1, "Large text");
            int rows = ps.executeUpdate();
    System.out.println(rows);
          }
          finally {
            ps.close();
          }
          con.commit();
        } catch (Exception e) {
          con.rollback();
    e.printStackTrace(System.out);
        } finally {
          con.close();
        }
    }

    public static void main(String args[]) throws Exception
    {
        Class.forName( "net.sourceforge.jtds.jdbc.Driver" );
        String url = "jdbc:jtds:sqlserver://server/Alin_Test";
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "");
//        props.setProperty("TDS", "4.2");
//        com.internetcds.util.Logger.setFilename(null);
        net.sourceforge.jtds.util.Logger.setActive(true);
        Connection con = DriverManager.getConnection(url, props);

//        PreparedStatement ps = con.prepareStatement("UPDATE dbo.GuidTest set value=? WHERE guid='{A86FD925-964D-42D9-8490-FE54057CA216}'");
//        ps.setString(1, "val");
//        System.out.println("Insert 1: "+ps.executeUpdate());
//        ps.clearParameters();
//        ps.setBoolean(1, true);
//        System.out.println("Insert 2: "+ps.executeUpdate());
        Statement s = con.createStatement();
        ResultSet rs = s.executeQuery("Select (SELECT SUM(col3) FROM FirstTable WHERE col1=3) z, col1 from FirstTable where col1=5");

        System.out.println(rs.getWarnings().getMessage());
        while( rs.next() )
            System.out.println("Eu.");

        con.close();
    }

    public static void main0(String args[]) throws Exception
    {
        Class.forName( "net.sourceforge.jtds.jdbc.Driver" );
        String url = "jdbc:jtds:sqlserver://server:1433/Alin_Test";
//        Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
//        String url = "jdbc:odbc:Alin_Test";
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "");
        props.setProperty("charset", "Cp1252");
        props.setProperty("TDS", "7.0");
        net.sourceforge.jtds.util.Logger.setActive(true);
        Connection con = DriverManager.getConnection(url, props);
//        ResultSet rss = con.getMetaData().getCatalogs();
//
//        System.out.println(rss.getMetaData().getColumnName(1));
//        while( rss.next() )
//            System.out.println(rss.getObject(1));
//        rss.close();

        System.out.println("Start");
        ResultSet rs = null;
        try
        {
//            PreparedStatement s = con.prepareStatement("exec sp_datatype_info");

//            PreparedStatement s = con.prepareStatement("INSERT INTO GuidTest (value) VALUES (?)\n"+
//                "SELECT * FROM GuidTest\n"+
//                "INSERT INTO GuidTest (value) VALUES (?)");
//            s.setString(1, "Gigi");
//            s.setString(2, "Gaga");
//            boolean b = s.execute();

            Statement s = con.createStatement(/*ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE*/);
            boolean b = s.execute("SELECT * FROM NVARCHAR_Table");

            while( b || s.getUpdateCount()>=0 )
            {
                if( b )
                {
                    rs = s.getResultSet();
                    System.out.println(rs.getMetaData().getColumnCount());

                    while( rs.next() )
                    {
                        System.out.println(rs.getObject(1));
                        System.out.println(rs.getString(2));
                    }
                }
                else
                    System.out.println(s.getUpdateCount());

                b = s.getMoreResults();
            }
        }
        catch( SQLException ex )
        {
            ex.printStackTrace();
        }

        con.close();
    }

    public static void main4(String args[]) throws Exception
    {
        Class.forName( "net.sourceforge.jtds.jdbc.Driver" );
        String url = "jdbc:jtds:sqlserver://server/Alin_Test";
        Properties props = new Properties();
        props.setProperty("user", "sa");
        props.setProperty("password", "");
        net.sourceforge.jtds.util.Logger.setActive(true);
        Connection con = DriverManager.getConnection(url, props);

        try
        {
            CallableStatement call = con.prepareCall("{call dbo.outputTest (?, ?) }");
            call.setInt(1, 12);
            call.registerOutParameter(2, Types.INTEGER);
            call.execute();
            System.out.println(call.getInt(2));
        }
        catch( SQLException ex )
        {
            ex.printStackTrace();
        }

        con.close();
    }
}