Java是如何快速煮成C 的（二）数据访问 1

来源：易妖游戏网

分享一下我老师大神的人工智能教程！零基础，通俗易懂！

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

在上两篇和中，我们开启了C#与Java中的话题之一:相似的方法。其实我写主这两篇文章的目的，有两个：
1、总结自己的学习历程，这个是主要的。
2、给c#转入java的同学一个快速熟悉的感觉，所以这个系列的名称也是“快速煮成”。
因为我对这两门语言仅限了解，对c#也仅限于熟悉，如有理解不妥之处，请指正。
今天我们看看这两种语言环境下纯粹的数据访问。
首先我再次声明：
1、本文不比较这两种语言的数据访问的性能差异。
2、本文不涉及各自的OR框架，如C#的ADO.NET Entity Framework,MVC,Nhibernate,spring.net，以及Java领域的Spring/Hibernate/Struts等第三方框架,只是纯粹的数据访问。
3、数据库采用MS SQL server 2008,其实也可以用mySQL,MySQL提供官方支持。oracle平时很少用,DB2没用过。
准备工作：一个用于测试的部门表DepartDemo,表结构如下：

[ruby]

Create database Db2010Demogouse Db2010Demogoif exists (select 1from sysobjectswhere id = object_id('DepartDemo')and type = 'U')drop table DepartDemogo/*==============================================================*//* Table: DepartDemo *//*==============================================================*/create table DepartDemo (PKID int identity(101,1),DName nvarchar(200) null,DCode nvarchar(500) null,Manager nvarchar(50) null,ParentID int null default 0,AddUser nvarchar(50) null,AddTime datetime null,ModUser nvarchar(50) null,ModTime datetime null,CurState smallint not null default 0,Remark nvarchar(500) null,F1 int not null default 0,F2 nvarchar(300) null,constraint PK_DEPARTDEMO primary key (PKID))go--插入一条测试数据insert into DepartDemoselect '国家统计局房产审计一科','0','胡不归',0,'DeomUser',getdate(),'','1900-01-01',1,'专业评估全国房价，为老百姓谋福祉',0,''--创建一个存储过程，在C#程序中用到IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DepartDemoSelectByPKID]') AND type='P')DROP PROCEDURE [dbo].[DepartDemoSelectByPKID]GOCREATE PROCEDURE [dbo].[DepartDemoSelectByPKID](@Pkid int)ASBEGIN TRANSelect * From [DepartDemo] Where [Pkid]=@PkidIF @@ERROR!=0BEGINROLLBACKENDELSEBEGINCOMMITENDGO

一、我们看看C#环境下一个数据访问的简单例子。

在vs2010中新建一控制台项目，结构如下：

相应的代码,

基础数据层：

Database.cs:

[c-sharp]

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace demo2010.database
{
public class DataBase
{
private static string connectionString = System.Configuration.ConfigurationManager.AppSettings["GlobalsConnString"];
public static string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
#region Helpers
internal protected static IDataReader GetReader(string commandText, SqlParameter[] p)
{
return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, commandText, p);
}
internal protected static IDataReader GetReader(string commandText)
{
return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, commandText);
}
internal protected static int NonQueryInt(string commandText, SqlParameter[] p)
{
return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, commandText, p);
}
internal protected static bool NonQueryBool(string commandText, SqlParameter[] p)
{
return NonQueryInt(commandText, p) > 0;
}
internal protected void RunSql(string commandText)
{
SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, commandText);
}
public static void ExecuteSQL(string commandText)
{
SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, commandText);
}
public static DataTable GetDataTable(string commandText)
{
return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, commandText);
}
public static DataTable GetDataTable(string commandText, CommandType commandType)
{
return SqlHelper.ExecuteDataTable(ConnectionString, commandType, commandText);
}
internal protected static string ReturnString(string commandText, SqlParameter[] p)
{
object result = SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.StoredProcedure, commandText, p);
return result.ToString();
}
internal protected static SqlParameter ReTurnStringValue
{
get
{
return SqlHelper.MakeOutParam("@ReTurnValue", SqlDbType.NVarChar, 200);
}
}
internal protected static SqlParameter ReTurnLongValue
{
get
{
return SqlHelper.MakeOutParam("@ReTurnValue", SqlDbType.BigInt, 8);
}
}
#endregion
}
}

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace demo2010.database{ public class DataBase { private static string connectionString = System.Configuration.ConfigurationManager.AppSettings["GlobalsConnString"]; public static string ConnectionString { get { return connectionString; } set { connectionString = value; } } #region Helpers internal protected static IDataReader GetReader(string commandText, SqlParameter[] p) { return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, commandText, p); } internal protected static IDataReader GetReader(string commandText) { return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, commandText); } internal protected static int NonQueryInt(string commandText, SqlParameter[] p) { return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, commandText, p); } internal protected static bool NonQueryBool(string commandText, SqlParameter[] p) { return NonQueryInt(commandText, p) > 0; } internal protected void RunSql(string commandText) { SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, commandText); } public static void ExecuteSQL(string commandText) { SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, commandText); } public static DataTable GetDataTable(string commandText) { return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, commandText); } public static DataTable GetDataTable(string commandText, CommandType commandType) { return SqlHelper.ExecuteDataTable(ConnectionString, commandType, commandText); } internal protected static string ReturnString(string commandText, SqlParameter[] p) { object result = SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.StoredProcedure, commandText, p); return result.ToString(); } internal protected static SqlParameter ReTurnStringValue { get { return SqlHelper.MakeOutParam("@ReTurnValue", SqlDbType.NVarChar, 200); } } internal protected static SqlParameter ReTurnLongValue { get { return SqlHelper.MakeOutParam("@ReTurnValue", SqlDbType.BigInt, 8); } } #endregion }}

DynamicBuilder.cs:

[c-sharp]

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;
using System.Reflection.Emit;
using System.Data;
namespace demo2010.database
{
/// <summary>
/// Tony 2008.8.28 Add
/// </summary>
/// <typeparam name="T"></typeparam>
public class DynamicBuilder<T>
{
private static readonly MethodInfo getValueMethod = typeof(IDataRecord).GetMethod("get_Item", new Type[] { typeof(int) });
private static readonly MethodInfo isDBNullMethod = typeof(IDataRecord).GetMethod("IsDBNull", new Type[] { typeof(int) });
private delegate T Load(IDataRecord dataRecord);
private const BindingFlags BINDING_FLAGS = BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase;//Added by Tony 2008.09.25
private Load handler;
private DynamicBuilder() { }
public T Build(IDataRecord dataRecord)
{
return handler(dataRecord);
}
public static DynamicBuilder<T> CreateBuilder(IDataRecord dataRecord)
{
DynamicBuilder<T> dynamicBuilder = new DynamicBuilder<T>();
DynamicMethod method = new DynamicMethod("DynamicCreate", typeof(T), new Type[] { typeof(IDataRecord) }, typeof(T), true);
ILGenerator generator = method.GetILGenerator();
LocalBuilder result = generator.DeclareLocal(typeof(T));
generator.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes));
generator.Emit(OpCodes.Stloc, result);
for (int i = 0; i < dataRecord.FieldCount; i++)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(dataRecord.GetName(i),BINDING_FLAGS);//performance'Maxvalue decrease 5% **** Test By Tony 2008.09.25
Label endIfLabel = generator.DefineLabel();
if (propertyInfo != null && propertyInfo.GetSetMethod() != null)
{
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, isDBNullMethod);
generator.Emit(OpCodes.Brtrue, endIfLabel);
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, getValueMethod);
generator.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i));
generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod());
generator.MarkLabel(endIfLabel);
}
}
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ret);
dynamicBuilder.handler = (Load)method.CreateDelegate(typeof(Load));
return dynamicBuilder;
}
}
}

using System;using System.Collections.Generic;using System.Data.SqlClient;using System.Reflection;using System.Reflection.Emit;using System.Data;namespace demo2010.database{ /// <summary> /// Tony 2008.8.28 Add /// </summary> /// <typeparam name="T"></typeparam> public class DynamicBuilder<T> { private static readonly MethodInfo getValueMethod = typeof(IDataRecord).GetMethod("get_Item", new Type[] { typeof(int) }); private static readonly MethodInfo isDBNullMethod = typeof(IDataRecord).GetMethod("IsDBNull", new Type[] { typeof(int) }); private delegate T Load(IDataRecord dataRecord); private const BindingFlags BINDING_FLAGS = BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase;//Added by Tony 2008.09.25 private Load handler; private DynamicBuilder() { } public T Build(IDataRecord dataRecord) { return handler(dataRecord); } public static DynamicBuilder<T> CreateBuilder(IDataRecord dataRecord) { DynamicBuilder<T> dynamicBuilder = new DynamicBuilder<T>(); DynamicMethod method = new DynamicMethod("DynamicCreate", typeof(T), new Type[] { typeof(IDataRecord) }, typeof(T), true); ILGenerator generator = method.GetILGenerator(); LocalBuilder result = generator.DeclareLocal(typeof(T)); generator.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes)); generator.Emit(OpCodes.Stloc, result); for (int i = 0; i < dataRecord.FieldCount; i++) { PropertyInfo propertyInfo = typeof(T).GetProperty(dataRecord.GetName(i),BINDING_FLAGS);//performance'Maxvalue decrease 5% **** Test By Tony 2008.09.25 Label endIfLabel = generator.DefineLabel(); if (propertyInfo != null && propertyInfo.GetSetMethod() != null) { generator.Emit(OpCodes.Ldarg_0); generator.Emit(OpCodes.Ldc_I4, i); generator.Emit(OpCodes.Callvirt, isDBNullMethod); generator.Emit(OpCodes.Brtrue, endIfLabel); generator.Emit(OpCodes.Ldloc, result); generator.Emit(OpCodes.Ldarg_0); generator.Emit(OpCodes.Ldc_I4, i); generator.Emit(OpCodes.Callvirt, getValueMethod); generator.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i)); generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod()); generator.MarkLabel(endIfLabel); } } generator.Emit(OpCodes.Ldloc, result); generator.Emit(OpCodes.Ret); dynamicBuilder.handler = (Load)method.CreateDelegate(typeof(Load)); return dynamicBuilder; } }}

SqlHelper.cs:(我们将最常用的SQL在大家熟悉的SqlHelper中。)

[c-sharp]

using System;
using System.Configuration;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
namespace demo2010.database
{
#region Disclaimer/Info
#endregion
/// <summary>
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient.
/// </summary>
public sealed class SqlHelper
{
#region private utility methods & constructors
//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelper()".
private SqlHelper() { }
/// <summary>
/// This method is used to attach array of SqlParameters to a SqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
/// <summary>
/// This method assigns an array of values to an array of SqlParameters.
/// </summary>
/// <param name="commandParameters">array of SqlParameters to be assigned values</param>
/// <param name="parameterValues">array of Components holding the values to be assigned</param>
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
//do nothing if we get no data
return;
}
// we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
//iterate through the SqlParameters, assigning the values from the corresponding position in the
//value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the SqlCommand to be prepared</param>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion private utility methods & constructors
#region DataHelpers
public static string CheckNull(object obj)
{
return (string)obj;
}
public static string CheckNull(DBNull obj)
{
return null;
}
#endregion
#region AddParameters
public static object CheckForNullString(string text)
{
if (text == null || text.Trim().Length == 0)
{
return string.Empty;
//return System.DBNull.Value;
}
else
{
return text;
}
}
public static object CheckForNullDateTime(object obj)
{
if (obj == null)
{
return Convert.ToDateTime("1900-01-01");
//return System.DBNull.Value;
}
else
{
return obj;
}
}
public static SqlParameter MakeInParam(string ParamName, object Value)
{
return new SqlParameter(ParamName, Value);
}
/// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
/// <summary>
/// Make stored procedure param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Direction">Parm direction.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
#endregion
#region ExecuteNonQuery
/// <summary>
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//finally, execute the command.
int retval = cmd.ExecuteNonQuery();
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, C
给我老师的人工智能教程打call！

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文

全部频道

Java是如何快速煮成C 的 （二） 数据访问 1

给我老师的人工智能教程打call！

Java是如何快速煮成C 的（二）数据访问 1