java 用jdbc:odbc和 jdbc驱动调用 sqlserver2000 数据库

最新推荐文章于 2025-05-26 11:15:57 发布

原创最新推荐文章于 2025-05-26 11:15:57 发布 · 604 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#java #Java #JAVA #jdbc #jdbcodbc

本文介绍了如何使用Java通过JDBC驱动和JDBC-ODBC桥接两种方式连接并操作SQLServer2000数据库，包括数据查询和插入。示例代码详细展示了连接数据库、执行SQL语句以及资源释放的过程。

1,jdbc驱动 sqlserver2000

package com.test;
import java.sql.*;

public class test1 {

public static void main(String[] args) {
       Connection ct = null; //先定义要用到的对象
        PreparedStatement ps =null;
      ResultSet rs = null;
      try
     {

               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//加载驱动
              ct = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;databaseName=test1","sa","sa");//创建连接,test1为数据库名sa为用户名第二个sa为密码
              ps = ct.prepareStatement("select * from goods");//java程序往数据库中发送sql语句
                rs = ps.executeQuery(); //因为是查询调用query()方法,r如果是执行增删查改则用update()方法
               while(rs.next())
              {
                     int goodsId = rs.getInt("goodsId");
                  String goodsName = rs.getString("goodsName");//结果集以此循环读出字段
                        int unitPrice = rs.getInt(3);
                   String categroy = rs.getString("categroy");
                  String provider = rs.getString("provider");
                  System.out.println(goodsId+" "+goodsName+" "+unitPrice+" "+categroy+" "+provider); //输出每个字段
              }

      } catch(Exception e)
        {
                e.printStackTrace();
          } finally
          {
            try
              {
                  if(null!=rs)
                   {
                       rs.close();//关闭资源，后打开的先关闭，为了健壮性，先判断是否已经关闭
                     }
                  if(null!=ps)
                  {
                   ps.close();
                      }
                    if(null!=ct)
                     {
                        ct.close();
                     }
                    }catch(Exception e)
                   {
                      e.printStackTrace();
                    }

}

2.jabc：odbc驱动sqlserver2000进行查询

package com.test;
import java.sql.*;
public class test3 {

public static void main(String[] args) {
      Connection ct = null;
    PreparedStatement ps = null;
      ResultSet rs = null;
  try
  {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        ct = DriverManager.getConnection("jdbc:odbc:mytest1","sa","sa");//这个jdbc:odbc不要驱动 jar包，但要建个数据源
       ps = ct.prepareStatement("select * from goods");
      rs = ps.executeQuery();
      while(rs.next())
     {
            System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt("unitprice")+" "+rs.getString(4)+" "+rs.getString("provider"));
      }

   }catch(Exception e)
     {
       e.printStackTrace();
  }finally
  {
   try
   {
        if(null!=rs)
       {
        rs.close();
       }
     if(null!=ps)
      {
        ps.close();
       }
       if(null!=ct)
      {
       ct.close();
    }

    }catch(Exception e)
   {
         e.printStackTrace();
     }


   }

}

}

3jdbc驱动sqlserver2000先进行插入再进行查询

package com.test;
import java.sql.*;
public class test2 {

public static void main(String[] args) {
       Connection ct = null;
     PreparedStatement ps = null;
     ResultSet rs = null;
  try
  {
          Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
         ct = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;databaseName=test1","sa","sa");
        ps = ct.prepareStatement("insert into goods values(?,?,?,?,?)");
       ps.setInt(1,47);
       ps.setString(2,"汽车3");
      ps.setInt(3,100);
      ps.setString(4,"日用品");
      ps.setString(5,"深圳");
      int i = ps.executeUpdate();
       if(1==i)
     {
         System.out.println("Insert ok!");
      }
    else
     {
        System.out.println("Insert error!");
      }
            ps = ct.prepareStatement("select * from goods");//这句很关键，否则还是在做插入木有进行查找
        rs=ps.executeQuery();
       while(rs.next())
      {
          int goodsId = rs.getInt(1);
         String goodsName = rs.getString("goodsName");
          int unitprice = rs.getInt("unitprice");
         String categroy = rs.getString("categroy");
        String provider = rs.getString("provider");
        System.out.println(goodsId+" "+goodsName+" "+unitprice+" "+categroy+" "+provider);

  }

   }catch(Exception e)
  {
        e.printStackTrace();
   }finally{
         try
       {
          if(null!=rs)
       {
           rs.close();
       }
       if(null!=ps)
    {
       ps.close();
      }
        if(null!=ct)
      {
          ct.close();
      }

     }catch(Exception e)
   {
         e.printStackTrace();
   }

}
}