SqlServer 2008相关知识

主要内容:

• PreparedStatement 与 Statement
• AutoCommit
• java获取表结构信息

一. Java中PreparedStatement和Statement的用法区别

• PreparedStatement接口继承Statement， PreparedStatement 实例包含已编译的 SQL 语句，所以其执行速度要快于 Statement 对象。

• 作为 Statement 的子类，PreparedStatement 继承了 Statement 的所有功能。三种方法
  execute、 executeQuery 和 executeUpdate 已被更改以使之不再需要参数

• 在JDBC应用中,如果你已经是稍有水平开发者,你就应该始终以PreparedStatement代替 Statement.也就是说,在任何时候都不要使用Statement.基于以下的原因:

1.代码的可读性和可维护性.
虽然用PreparedStatement来代替Statement会使代码多出几行,但这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次:

stmt = conn.createStatement();
stmt.executeUpdate("insert into tb_name (col1,col2,col4) values ('"+var1+"','"+var2+"',"+var3+",'"+var4+"')");//stmt是Statement对象实例

perstmt = con.prepareStatement("insert into tb_name (col1,col4) values (?,?,?)");
perstmt.setString(1,var1);
perstmt.setString(2,var2);
perstmt.setString(3,var3);
perstmt.setString(4,var4);
perstmt.executeUpdate(); //prestmt是 PreparedStatement 对象实例 

不用我多说,对于第一种方法.别说其他人去读你的代码,就是你自己过一段时间再去读,都会觉得伤心.

2.PreparedStatement尽最大可能提高性能.
语句在被DB的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中(相当于一个涵数)就会得到执行.这并不是说只有一个Connection中多次执行的预编译语句被缓存,而是对于整个DB中,只要预编译的语句语法和缓存中匹配.那么在任何时候就可以不需要再次编译而可以直接执行.而statement的语句中,即使是相同一操作,而由于每次操作的数据不同所以使整个语句相匹配的机会极小,几乎不太可能匹配.比如:
insert into tb_name (col1,col2) values (‘11’,’22’);
insert into tb_name (col1,’23’);
即使是相同操作但因为数据内容不一样,所以整个个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存.

当然并不是所以预编译语句都一定会被缓存,数据库本身会用一种策略,比如使用频度等因素来决定什么时候不再缓存已有的预编译结果.以保存有更多的空间存储新的预编译语句.

3.最重要的一点是极大地提高了安全性.

即使到目前为止,仍有一些人连基本的恶义SQL语法都不知道.

String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";

如果我们把[’ or ‘1’ = ‘1]作为varpasswd传入进来.用户名随意,看看会成为什么?

select * from tb_name = ‘随意’ and passwd = ” or ‘1’ = ‘1’;
因为’1’=’1’肯定成立,所以可以任何通过验证.更有甚者:
把[‘;drop table tb_name;]作为varpasswd传入进来,则:
select * from tb_name = ‘随意’ and passwd = ”;drop table tb_name;有些数据库是不会让你成功的,但也有很多数据库就可以使这些语句得到执行.

而如果你使用预编译语句.你传入的任何内容就不会和原来的语句发生任何匹配的关系.只要全使用预编译语句,你就用不着对传入的数据做任何过虑.而如果使用普通的statement,有可能要对drop,;等做费尽心机的判断和过虑.

上面的几个原因,还不足让你在任何时候都使用PreparedStatement吗?

二. Autocommit用法

setAutoCommit总的来说就是保持数据的完整性，一个系统的更新操作可能要涉及多张表，需多个SQL语句进行操作

循环里连续的进行插入操作，如果你在开始时设置了：conn.setAutoCommit(false);
最后才进行conn.commit(),这样你即使插入的时候报错，修改的内容也不会提交到数据库，
而如果你没有手动的进行setAutoCommit(false);
出错时就会造成，前几条插入，后几条没有
会形成脏数据~~

setAutoCommit(false)的误用
（设定setAutoCommit(false)没有在catch中进行Connection的rollBack操作，操作的表就会被锁住，造成数据库死锁）：
误用Connection.setAutoCommit导致的数据库死锁问题。
系统在发布到用户的的服务器了，运行一段时间偶尔出现某些功能不能正常运行，甚至不能自动恢复，严重导致服务器当机，表现为服务器不响应用户的请求，数据库有大量的锁。在服务器重启后才能恢复正常。今天通遍的查看了一下代码，初步分析了原因，记录了下来，跟大家交流交流。
先看下面一段有问题的代码：

1       Connection con = null;  
2      try{  
3          con = getConnection();  
4          con.setAutoCommit(false);  
           /* 5 * update USER set name=’winson’ where id=’000001’; */  
6          con.commit();  
7       }finally{  
8          if(con!=null){  
9              try {  
10                 con.close();  
11             } catch (SQLException e) {  
12                 e.printStackTrace();  
13             }  
           }  
       }

分析：问题就出现在第4行，写代码的人把数据库连接con 设置成非自动提交，但没有在执行出现异常的时候进行回滚。如果在执行第5行的时候出现异常，con既没有提交也没有回滚，表USER就会被锁住(如果oracle数据库就是行锁)，而这个锁却没有机会释放。有人会质疑，在执行con.close()的时候不会释放锁吗？因为如果应用服务器使用了数据库连接池，连接不会被断开。

参考正确的写法应该是：

Connection con = null;  
       try{  
           con = getConnection();  
           con.setAutoCommit(false);  
           /* * do what you want here. */  
           con.commit();  
        }catch(Throwable e){  
           if(con!=null){  
               try {  
                   con.rollback();  
               } catch (SQLException e1) {  
                   e1.printStackTrace();  
               }  
           }  

throw new RuntimeException(e);  
        }finally{  
           if(con!=null){  
               try {  
                   con.close();  
               } catch (SQLException e) {  
                   e.printStackTrace();  
               }  
           }  
       }

三. 获取表结构信息

参考：http://www.voidcn.com/article/p-vrjivptp-bky.html

jdbc有的。假设有个con

DatabaseMetaData dbmd = con.getMetaData(); 
rs = dbmd.getColumns(con.getCatalog(),schema,tableName,null); 
rs.getString(DATA_TYPE) Java.sql.Types 的 SQL 类型 
rs.getString(COLUMN_SIZE) 列的大小。对于 char 或 date 类型，列的大小是最大字符数，对于 numeric 和 decimal 类型，列的大小就是精度。 
rs.getString(DECIMAL_DIGITS) 小数部分的位数

JDBC中通过MetaData来获取具体的表的相关信息。可以查询数据库中的有哪些表，表有哪些字段，字段的属性等等。MetaData中通过一系列getXXX函数，将这些信息存放到ResultSet里面，然后返回给用户。关于MetaData的说明网上也有不少，这里我只是从我自身学习的角度来记录一下简单使用JDBC以及获取数据表相关信息的方法。
首先，http://hometown.aol.com/kgb1001001/Articles/JDBCMetadata/JDBC_Metadata.htm 这里有篇名字叫做《Understanding JDBC MetaData》的文章，讲解得不错。

下面就是我的JDBC下的获取表信息的代码了。我是以MySQL 5.0作为测试平台的。
1.JDBC连接MYSQL的代码很标准，很简单。

class.forName("com.mysql.jdbc.Driver").newInstance(); 
Connection conn = DriverManager 
.getConnection("jdbc:mysql://localhost/test?user=root&password=123456");

2.下面就是获取表的信息。

m_DBMetaData = m_Connection.getMetaData(); 
ResultSet tableRet = m_DBMetaData.getTables(null,"%",m_TableName,new String[]{"TABLE"});

其中”%”就是表示*的意思，也就是任意所有的意思。其中m_TableName就是要获取的数据表的名字，如果想获取所有的表的名字，就可以使用”%”来作为参数了。

3.提取表的名字。

while(tableRet.next) System.out.println(tableRet.getString("TABLE_NAME"));

通过getString(“TABLE_NAME”)，就可以获取表的名字了。
从这里可以看出，前面通过getTables的接口的返回，JDBC是将其所有的结果，保存在一个类似table的内存结构中，而其中TABLE_NAME这个名字的字段就是每个表的名字。

4.提取表内的字段的名字和类型

String columnName; 
String columnType; 
ResultSet colRet = m_DBMetaData.getColumns(null,"%"); 
while(colRet.next()) { 
columnName = colRet.getString("COLUMN_NAME"); 
columnType = colRet.getString("TYPE_NAME"); 
int datasize = colRet.getInt("COLUMN_SIZE"); 
int digits = colRet.getInt("DECIMAL_DIGITS"); 
int nullable = colRet.getInt("NULLABLE"); 
System.out.println(columnName+" "+columnType+" "+datasize+" "+digits+" "+ 
nullable); 
}

JDBC里面通过getColumns的接口，实现对字段的查询。跟getTables一样，”%”表示所有任意的（字段），而m_TableName就是数据表的名字。

getColumns的返回也是将所有的字段放到一个类似的内存中的表，而COLUMN_NAME就是字段的名字，TYPE_NAME就是数据类型，比如”int”,”int unsigned”等等，COLUMN_SIZE返回整数，就是字段的长度，比如定义的int(8)的字段，返回就是8，最后NULLABLE，返回1就表示可以是Null,而0就表示Not Null。

通过jdbc获取数据库中的表结构 主键 各个表字段类型及应用生成实体类
1、JDBC中通过MetaData来获取具体的表的相关信息。可以查询数据库中的有哪些表，表有哪些字段，字段的属性等等。MetaData中通过一系列getXXX函数，将这些信息存放到ResultSet里面，然后返回给用户。关于MetaData的说明网上也有不少，这里我只是从我自身学习的角度来记录一下简单使用JDBC以及获取数据表相关信息的方法。

DatabaseMetaData dbmd = con.getMetaData(); 
rs = dbmd.getColumns(con.getCatalog(),null); 
rs.getString(DATA_TYPE) // java.sql.Types 的 SQL 类型 
rs.getString(COLUMN_SIZE) //列的大小。对于 char 或 date 类型，列的大小是最大字符数，对于 numeric 和 decimal 类型，列的大小就是精度。 
rs.getString(DECIMAL_DIGITS) //小数部分的位数

2、下面就是我的JDBC下的获取表信息的代码了。我是以MySQL 5.0作为测试平台的。可以通过下面四个步骤来实现：

//1. JDBC连接MYSQL的代码很标准。 
class.forName("com.mysql.jdbc.Driver").newInstance(); 
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=123456");

//2. 下面就是获取表的信息。 
m_DBMetaData = m_Connection.getMetaData(); 
ResultSet tableRet = m_DBMetaData.getTables(null,new String[]{"TABLE"}); 
/*其中"%"就是表示*的意思，也就是任意所有的意思。其中m_TableName就是要获取的数据表的名字，如果想获取所有的表的名字，就可以使用"%"来作为参数了。*/

//3. 提取表的名字。 
while(tableRet.next) System.out.println(tableRet.getString("TABLE_NAME"));

/*通过getString("TABLE_NAME")，就可以获取表的名字了。 从这里可以看出，前面通过getTables的接口的返回，JDBC是将其所有的结果，保存在一个类似table的内存结构中，而其中TABLE_NAME这个名字的字段就是每个表的名字。*/

//4. 提取表内的字段的名字和类型 
String columnName; 
String columnType; 
ResultSet colRet = m_DBMetaData.getColumns(null,"%"); 
while(colRet.next()) { 
　　columnName = colRet.getString("COLUMN_NAME"); 
　　columnType = colRet.getString("TYPE_NAME"); 
　　int datasize = colRet.getInt("COLUMN_SIZE"); 
　　int digits = colRet.getInt("DECIMAL_DIGITS"); 
　　int nullable = colRet.getInt("NULLABLE"); 
　　System.out.println(columnName+" "+columnType+" "+datasize+" "+digits+" "+ nullable); 
}

/*JDBC里面通过getColumns的接口，实现对字段的查询。跟getTables一样，"%"表示所有任意的（字段），而m_TableName就是数据表的名字。 getColumns的返回也是将所有的字段放到一个类似的内存中的表，而COLUMN_NAME就是字段的名字，TYPE_NAME就是数据类型，比如"int","int unsigned"等等，COLUMN_SIZE返回整数，就是字段的长度，比如定义的int(8)的字段，返回就是8，最后NULLABLE，返回1就表示可以是Null,而0就表示Not Null。*/

每个列描述都有以下列：

TABLE_CAT String => 表类别（可为 null） 
TABLE_SCHEM String => 表模式（可为 null） 
TABLE_NAME String => 表名称 
COLUMN_NAME String => 列名称 
DATA_TYPE int => 来自 java.sql.Types 的 SQL 类型 
TYPE_NAME String => 数据源依赖的类型名称，对于 UDT，该类型名称是完全限定的 
COLUMN_SIZE int => 列的大小。 
BUFFER_LENGTH 未被使用。 
DECIMAL_DIGITS int => 小数部分的位数。对于 DECIMAL_DIGITS 不适用的数据类型，则返回 Null。 
NUM_PREC_RADIX int => 基数（通常为 10 或 2） 
NULLABLE int => 是否允许使用 NULL。 
columnNoNulls - 可能不允许使用 NULL 值 
columnNullable - 明确允许使用 NULL 值 
columnNullableUnknown - 不知道是否可使用 null 
REMARKS String => 描述列的注释（可为 null） 
COLUMN_DEF String => 该列的默认值，当值在单引号内时应被解释为一个字符串（可为 null） 
SQL_DATA_TYPE int => 未使用 
SQL_DATETIME_SUB int => 未使用 
CHAR_OCTET_LENGTH int => 对于 char 类型，该长度是列中的最大字节数 
ORDINAL_POSITION int => 表中的列的索引（从 1 开始） 
IS_NULLABLE String => ISO 规则用于确定列是否包括 null。 
YES --- 如果参数可以包括 NULL 
NO --- 如果参数不可以包括 NULL 
空字符串 --- 如果不知道参数是否可以包括 null 
SCOPE_CATLOG String => 表的类别，它是引用属性的作用域（如果 DATA_TYPE 不是 REF，则为 null） 
SCOPE_SCHEMA String => 表的模式，它是引用属性的作用域（如果 DATA_TYPE 不是 REF，则为 null） 
SCOPE_TABLE String => 表名称，它是引用属性的作用域（如果 DATA_TYPE 不是 REF，则为 null） 
SOURCE_DATA_TYPE short => 不同类型或用户生成 Ref 类型、来自 java.sql.Types 的 SQL 类型的源类型（如果 DATA_TYPE 不是 DISTINCT 或用户生成的 REF，则为 null） 
IS_AUTOINCREMENT String => 指示此列是否自动增加 
YES --- 如果该列自动增加 
NO --- 如果该列不自动增加 
空字符串 --- 如果不能确定该列是否是自动增加参数 
COLUMN_SIZE 列表示给定列的指定列大小。对于数值数据，这是最大精度。对于字符数据，这是字符长度。对于日期时间数据类型，这是 String 表示形式的字符长度（假定允许的最大小数秒组件的精度）。对于二进制数据，这是字节长度。对于 ROWID 数据类型，这是字节长度。对于列大小不适用的数据类型，则返回 Null。

参数：

catalog - 类别名称；它必须与存储在数据库中的类别名称匹配；该参数为 "" 表示获取没有类别的那些描述；为 null 则表示该类别名称不应该用于缩小搜索范围
schemaPattern - 模式名称的模式；它必须与存储在数据库中的模式名称匹配；该参数为 "" 表示获取没有模式的那些描述；为 null 则表示该模式名称不应该用于缩小搜索范围
tableNamePattern - 表名称模式；它必须与存储在数据库中的表名称匹配
columnNamePattern - 列名称模式；它必须与存储在数据库中的列名称匹配

3、获取所有表

String catalog = conn.getCatalog(); //catalog 其实也就是数据库名 
ResultSet tablesResultSet = dbMetaData.getTables(catalog,null,new String[]{"TABLE"});  
while(tablesResultSet.next()){  
    String tableName = tablesResultSet.getString("TABLE_NAME");  
}

tablesResultSet 中有以下列：

TABLE_CAT String => 表类别（可为 null）
TABLE_SCHEM String => 表模式（可为 null）
TABLE_NAME String => 表名称
TABLE_TYPE String => 表类型。典型的类型是 "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"。
REMARKS String => 表的解释性注释
TYPE_CAT String => 类型的类别（可为 null）
TYPE_SCHEM String => 类型模式（可为 null）
TYPE_NAME String => 类型名称（可为 null）
SELF_REFERENCING_COL_NAME String => 有类型表的指定 "identifier" 列的名称（可为 null）
REF_GENERATION String => 指定在 SELF_REFERENCING_COL_NAME 中创建值的方式。这些值为 "SYSTEM"、"USER" 和 "DERIVED"。（可能为 null）

4、某个表的主键

String tableName = ...;  
ResultSet primaryKeyResultSet = dbMetaData.getPrimaryKeys(catalog,null,tableName);  
while(primaryKeyResultSet.next()){  
    String primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");  
}

primayKeyResultSet 有以下几列： 

TABLE_CAT String => 表类别（可为 null）
TABLE_SCHEM String => 表模式（可为 null）
TABLE_NAME String => 表名称
COLUMN_NAME String => 列名称
KEY_SEQ short => 主键中的序列号（值 1 表示主键中的第一列，值 2 表示主键中的第二列）。
PK_NAME String => 主键的名称（可为 null）

5、某个表的外键

ResultSet foreignKeyResultSet = dbMetaData.getImportedKeys(catalog,tableName);  
while(foreignKeyResultSet.next()){  
    String fkColumnName = foreignKeyResultSet.getString("FKCOLUMN_NAM");  
    String pkTablenName = foreignKeyResultSet.getString("PKTABLE_NAME");  
    String pkColumnName = foreignKeyResultSet.getString("PKCOLUMN_NAME");  
}

foreignKeyResultSet 有以下几列：

PKTABLE_CAT String => 被导入的主键表类别（可为 null）
PKTABLE_SCHEM String => 被导入的主键表模式（可为 null）
PKTABLE_NAME String => 被导入的主键表名称
PKCOLUMN_NAME String => 被导入的主键列名称
FKTABLE_CAT String => 外键表类别（可为 null）
FKTABLE_SCHEM String => 外键表模式（可为 null）
FKTABLE_NAME String => 外键表名称
FKCOLUMN_NAME String => 外键列名称
KEY_SEQ short => 外键中的序列号（值 1 表示外键中的第一列，值 2 表示外键中的第二列）
UPDATE_RULE short => 更新主键时外键发生的变化
DELETE_RULE short => 删除主键时外键发生的变化
PK_NAME String => 主键的名称（可为 null）
FK_NAME String => 外键的名称（可为 null）
DEFERRABILITY short => 是否可以将对外键约束的评估延迟到提交时间

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!