- How server side call sql server SP
- How client send parameter to server
- How server reply to client
- Ref article
Setting up a Thrift Server
https://medium.com/@saipeddy/setting-up-a-thrift-server-4eb0c55c11f0
For all the girls I loved
Setting up a Thrift Server
https://medium.com/@saipeddy/setting-up-a-thrift-server-4eb0c55c11f0
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
用 command line 执行 :
1 |
mvn install:install-file -Dfile=sqljdbc42.jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar |
此方法来源于 :
https://stackoverflow.com/questions/19537396/missing-artifact-com-microsoft-sqlserversqljdbc4jar4-0
执行成功的标志是: 可以在本地maven repository 看到 sqljdbc
1 |
<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> |
1 |
import java.sql.*; |
如果要持续的调用sql sp , 就不能每一次都新建connection , 要使用 connection pool , 选用 apache 的 dbcp 库
1 2 3 4 5 |
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1</version> </dependency> |
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(); } } |
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 .
https://www.javatips.net/blog/dbcp-connection-pooling-example
https://stackoverflow.com/questions/14467480/connection-pooling-with-apache-dbcp