- 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 执行 :
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
1 |
<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> |
- 需要用到 sql server 时 ,加这句即可
1 |
import java.sql.*; |
- 各种调用的例子, 包括直接执行sql语句,调用store procedure (带输入和输出参数) , 见这个网址, 例子比较全
如果要持续的调用sql sp , 就不能每一次都新建connection , 要使用 connection pool , 选用 apache 的 dbcp 库
- 如何引用: 在 pom.xml 加
1 2 3 4 5 |
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1</version> </dependency> |
- 在 package 新建 DataSource.java 文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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
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