sql server – Records And Memorys

download “Microsoft JDBC Driver 6.0 for SQL Server” from this site :

https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

解压后，到这个目录 sqljdbc_6.0.8112.200_enu.tar\sqljdbc_6.0.8112.200_enu\sqljdbc_6.0\enu\jre8

用 command line 执行：

mvn install:install-file -Dfile=sqljdbc42.jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar

1	mvn install:install-file -Dfile=sqljdbc42.jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar

这是因为maven没有sqljdbc的repository ，必须手动把 sqljdbc42.jar 加到本地的maven repository

此方法来源于：

https://stackoverflow.com/questions/19537396/missing-artifact-com-microsoft-sqlserversqljdbc4jar4-0

执行成功的标志是：可以在本地maven repository 看到 sqljdbc

然后，在 maven 的pom.xml 文件加入dependency

<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency>

1	<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency>

需要用到 sql server 时，加这句即可

import java.sql.*;

1	import java.sql.*;

各种调用的例子，包括直接执行sql语句，调用store procedure (带输入和输出参数) ，见这个网址，例子比较全

https://programmaticponderings.com/2012/08/24/calling-sql-server-stored-procedures-with-java-using-jdbc

如果要持续的调用sql sp , 就不能每一次都新建connection , 要使用 connection pool , 选用 apache 的 dbcp 库

如何引用：在 pom.xml 加

<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1</version>
</dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-dbcp2</artifactId>

</dependency>

在 package 新建 DataSource.java 文件

package org.tinyradius.test;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;

public class DataSource {

private static DataSource datasource;
private BasicDataSource ds;

private DataSource() throws IOException, SQLException, PropertyVetoException {
ds = new BasicDataSource();
ds.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

ds.setUrl("jdbc:sqlserver://sqlhostname:port;databaseName=xxx;user=sa;password=xxxxxxxxx");

ds.setInitialSize(5);
ds.setMaxTotal(5);

ds.setMaxOpenPreparedStatements(180);
}

public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
if (datasource == null) {
datasource = new DataSource();
return datasource;
} else {
return datasource;
}
}

public Connection getConnection() throws SQLException {
return this.ds.getConnection();
}
}

package org.tinyradius.test;

import java.beans.PropertyVetoException;

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;

public class DataSource {

private static DataSource datasource;

private BasicDataSource ds;

private DataSource() throws IOException, SQLException, PropertyVetoException {

ds = new BasicDataSource();

ds.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

ds.setUrl("jdbc:sqlserver://sqlhostname:port;databaseName=xxx;user=sa;password=xxxxxxxxx");

ds.setInitialSize(5);

ds.setMaxTotal(5);

ds.setMaxOpenPreparedStatements(180);

}

public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {

if (datasource == null) {

datasource = new DataSource();

return datasource;

} else {

return datasource;

}

public Connection getConnection() throws SQLException {

return this.ds.getConnection();

}

try to call a sp with one input and output parameter

package com.worldhubcom.testJDBC;

import java.sql.*;

public class App 
{
    public static void main( String[] args )
    {
	    CallableStatement cstmt = null;

	    // Declare the JDBC objects.
            Connection con = null;
		
	    String sStatus =null;
			 
	    try {
	    	String username = "userLY";
	    	
	    	con = DataSource.getInstance().getConnection();
	      	    	
	         
	         System.out.println("connection success \n");
	         
	        cstmt = con.prepareCall(
	                "{call sp_simple_sp_ly(?,?)}",
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_READ_ONLY);
	 
	        //cstmt.setString("I_username", username);
	        cstmt.setString("I_username", username);	        
	        cstmt.registerOutParameter("O_sMsg", java.sql.Types.VARCHAR);
	        
	        
	        cstmt.execute();
	        
	        System.out.println("exec success \n");
	        
	        sStatus = cstmt.getNString("O_sMsg");
	        
	    	System.out.println("sStatus:\n" + sStatus);

	    	
	    	
            cstmt.close();
            con.close();
            
	    	
	 //   run one more time and see if getconnection still spend much time 
	    	
	    	con = DataSource.getInstance().getConnection();
	           
//	    	
//	         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
//	         con = DriverManager.getConnection(connectionUrl);  
	    	
	         
	         System.out.println("connection success \n");
	         
	        cstmt = con.prepareCall(
	                "{call sp_simple_sp_ly(?,?)}",
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_READ_ONLY);
	 
	        //cstmt.setString("I_username", username);
	        cstmt.setString("I_username", username);	        
	        cstmt.registerOutParameter("O_sMsg", java.sql.Types.VARCHAR);
	        
	        
	        cstmt.execute();
	        
	        System.out.println("exec success \n");
	        
	        sStatus = cstmt.getNString("O_sMsg");
	        
	    	System.out.println("sStatus:\n" + sStatus);

	    	
	    	
	 
	    } catch (Exception ex) {
	    	System.out.println("exec sp raise error \n");
	    } finally {
	        if (cstmt != null) {
	            try {
	                cstmt.close();
	                con.close();
	            } catch (SQLException ex) {
	              
	            }
	        }
	    	
	    	
	    }
	    
	    //return sStatus;
	
	    
    	
    	
    }   //end of public main
}

100

101

package com.worldhubcom.testJDBC;

import java.sql.*;

public class App

{

public static void main( String[] args )

{

CallableStatement cstmt = null;

// Declare the JDBC objects.

Connection con = null;

String sStatus =null;

try {

String username = "userLY";

con = DataSource.getInstance().getConnection();

System.out.println("connection success \n");

cstmt = con.prepareCall(

"{call sp_simple_sp_ly(?,?)}",

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);

//cstmt.setString("I_username", username);

cstmt.setString("I_username", username);

cstmt.registerOutParameter("O_sMsg", java.sql.Types.VARCHAR);

cstmt.execute();

System.out.println("exec success \n");

sStatus = cstmt.getNString("O_sMsg");

System.out.println("sStatus:\n" + sStatus);

cstmt.close();

con.close();

// run one more time and see if getconnection still spend much time

con = DataSource.getInstance().getConnection();

// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

// con = DriverManager.getConnection(connectionUrl);

System.out.println("connection success \n");

cstmt = con.prepareCall(

"{call sp_simple_sp_ly(?,?)}",

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);

//cstmt.setString("I_username", username);

cstmt.setString("I_username", username);

cstmt.registerOutParameter("O_sMsg", java.sql.Types.VARCHAR);

cstmt.execute();

System.out.println("exec success \n");

sStatus = cstmt.getNString("O_sMsg");

System.out.println("sStatus:\n" + sStatus);

} catch (Exception ex) {

System.out.println("exec sp raise error \n");

} finally {

if (cstmt != null) {

try {

cstmt.close();

con.close();

} catch (SQLException ex) {

}

//return sStatus;

} //end of public main

}

from running , you can find when the second trying , this code

con = DataSource.getInstance().getConnection();

1	con = DataSource.getInstance().getConnection();

is much more faster than the first time .

apache dbcp 的例子见

https://www.javatips.net/blog/dbcp-connection-pooling-example

basicDataSource 是否已包含pool的特性？已有，详细解释见：

https://stackoverflow.com/questions/14467480/connection-pooling-with-apache-dbcp

Category: sql server

How to use thrift call sql server procedure and return result to client

How to use sql server jdbc in java application (for win 10)