jdbc

Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR

public class SQLServerUnicodeDialect extends org.hibernate.dialect.SQLServerDialect { public SQLServerUnicodeDialect() { super(); registerColumnType(Types.CHAR, “nchar(1)”); registerColumnType(Types.LONGVARCHAR, “nvarchar(max)” ); registerColumnType(Types.VARCHAR, 4000, “nvarchar($l)”); registerColumnType(Types.VARCHAR, “nvarchar(max)”); registerColumnType(Types.CLOB, “nvarchar(max)” ); registerColumnType(Types.NCHAR, “nchar(1)”); registerColumnType(Types.LONGNVARCHAR, “nvarchar(max)”); registerColumnType(Types.NVARCHAR, 4000, “nvarchar($l)”); registerColumnType(Types.NVARCHAR, “nvarchar(max)”); registerColumnType(Types.NCLOB, “nvarchar(max)”); registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName()); registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName()); registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName()); registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName() ); } }

Call stored procedure with table-valued parameter from java

This is documented here in the JDBC driver manual. In your case, you’d have to do this: try (SQLServerCallableStatement stmt = (SQLServerCallableStatement) con.prepareCall(“{call test(?)}”)) { SQLServerDataTable table = new SQLServerDataTable(); sourceDataTable.addColumnMetadata(“n”, java.sql.Types.INTEGER); sourceDataTable.addRow(9); sourceDataTable.addRow(12); sourceDataTable.addRow(27); sourceDataTable.addRow(37); stmt.setStructured(1, “dbo.integer_list_tbltype”, table); } I’ve also recently documented this in an article.

How to avoid storing passwords in the clear for tomcat’s server.xml Resource definition of a DataSource?

As said before encrypting passwords is just moving the problem somewhere else. Anyway, it’s quite simple. Just write a class with static fields for your secret key and so on, and static methods to encrypt, decrypt your passwords. Encrypt your password in Tomcat’s configuration file (server.xml or yourapp.xml…) using this class. And to decrypt the … Read more

PostgreSQL jsonb, `?` and JDBC

As a workaround to avoid the ? operator, you could create a new operator doing exactly the same. This is the code of the original operator: CREATE OPERATOR ?( PROCEDURE = jsonb_exists, LEFTARG = jsonb, RIGHTARG = text, RESTRICT = contsel, JOIN = contjoinsel); SELECT ‘{“a”:1, “b”:2}’::jsonb ? ‘b’; — true Use a different name, … Read more

Searching between dates in SQL with JDBC?

Use quotes around your dates: rs = stmt.executeQuery(“SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date ” + “FROM sales, customers ” + “WHERE sales.CardRecordID = customers.CardRecordID ” + “AND customers.Name=”Cash Sales” ” + “AND sales.Date BETWEEN ‘” + sdate + “‘ AND ‘” + edate + “‘ ” + “ORDER BY sales.ShipToAddress ASC, sales.Date DESC” + “;”); Or it … Read more

JDBC MySQL connection using Unix Socket

If you want to use UNIX sockets with the Mysql JDBC Connector/J you need to provide a socketFactory. jdbc:mysql:///?user=test&password=test&socketFactory=<classname>&<socket>=/tmp/mysql.sock So this will vary with the implementation you use. By default, Mysql does not ship with any implementation for that, just provides an example for such a factory in it’s source-code. There is an existing UNIX … Read more