javacream

Normal 0 21 false false false MicrosoftInternetExplorer4 public static String toMysqlDateStr(Date date){ if (date== null ) return "NULL" ; SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); return '\'' +sdf.format(date)+ '\'' ; }

public static String toMysqlDateStr(Date date){ if (date==null) return "NULL"; SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); return sqlValueWithQuotas(sdf.format(date)); } public static String sqlValueWithQuotas(Object obj){ if ( obj == null ) return "NULL"; String str = obj.toString(); str.replaceAll("'", "\\'"); str = '\''+str+'\''; return str; }

In a Database Server , a transaction is a sequence of operations. Those operations are considered to be a single unit of work which either finish all or nothing , that is a transaction is atomic either lives or never exists. For example you when your customer make a payment , you insert that payment into the payment table and update customer's balance in the customer table. If your update failse after the insert, you have to delete the payment to retry the operation. To avoid this we use transctions: begin; insert into customer_payment values(100,now(),12.0); update customer set balance = balance+12.0 where customer_id = 100"; commit; or something got wrong rollback; These two sql statements above will take affect into tables when you commit, if you rollback there will be no effect no update and no insert. By default , every database in JDBC commits automatically. If you want to make use of transactions , you have to turn autocommit off. Be caref...

Connection pooling is a cool mechanism because it allows to us reuse db connections. In a DB server a database connection is an expensive thing because it consumes system resources. Also connection creation and closing are time consuming operations. Connection Pooling helps us to reduce number of concurrent connections and to escape from connection open/close by keeping connections alive. Apache Tomcat is a great and ligthweight Java Application server for both development and production. Tomcat has an JDBC Connection Pooling mechanism. This feaute is configured in /META-INF/context.xml. Here is a sample: <?xml version="1.0" encoding="UTF-8"?> <Context docBase="/MyApp" path="/MyApp" reloadable="true"> <Resource name="jdbc/mydbresource" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" username="myusername" password=...

Here is a simple piece of code explaining Java JDBC with Mysql. Our case is to send email to customers who have negative balance. You cannot send email to a customer more than once a day. First we select customer list to send email , try to send their balance and update last_email_sent_date if mail sent. Our mysql server is on localhost and port 3306 (mysql default port) We have to grant access to required database or objects for creating a jdbc connection to server. here is the sample Mysql Command: grant all on customer to 'dbuser'@'localhost' identified by 'dbpass'; which gives full access on all objects on customer database. Be carefull about that ! ----------------------------------------- try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { //driver not found e.printStackTrace(); System.exit(-1); } Connection con = null; Statement stmt = null; ResultSet rs = null; try { con = DriverManage...