Refresher Course JDBC: Connect to database using configuration file properties, database access picture, batch processing, timestamp, things rollback, etc.

Connect to the database using the configuration file properties

First create a file custom file name, but the suffix name must be changed to. properties (not case): config.properties;

Then double-click config.properties to edit: This file data is stored according to the key-value pairs: We can store some connection strings attached to the database in this file, and then use the time to directly read the configuration file, when the replacement is convenient to transplant and modify.

Name value

driver                                                                                   Com.microsoft.sqlserver.jdbc.SQLServerDriver

con                                                                                      jdbc\:sqlserver\://localhost\:1433; Databasename\=test

User SA

Pwd

Then use the time:

public class Testmain {
Connection Con=null;
Properties Pro=new properties ();
Public Testmain () throws IOException, ClassNotFoundException, SQLException
{
Pro.load (TestMain.class.getClassLoader (). getResourceAsStream ("config.properties"));
Load Driver
Class.forName (Pro.getproperty ("Driver"));
Create a connection
Con=drivermanager.getconnection (Pro.getproperty ("con"), Pro.getproperty ("user"), Pro.getproperty ("pwd");

}

Test upload Image

    @Test
    public void setimg () throws SQLException, filenotfoundexception{
     string sql= "INSERT into [Test]. [dbo]. [User] ([Name],[age],[image]) VALUES (?,?,?) ";
     preparedstatement ps=con.preparestatement (SQL);
     ps.setobject (1, "Test name");
     ps.setobject (2,22);
     fileinputstream fis=new fileinputstream (New File ("d:\\img\\picture1.jpg"));//read from D-disk img
     ps.setbinarystream (3,fis);
     int num=ps.executeupdate ();
     system.out.println ("num is:" +num);  
   }

Take pictures and write to the hard drive folder
@Test
public void Getima () throws SQLException, ioexception{
Statement state=con.createstatement ();
String sql= "SELECT [Image] from [User] where id=6";
ResultSet rs=state.executequery (SQL);
Rs.next ();
InputStream Input=rs.getbinarystream (1);
OutputStream out=new FileOutputStream ("d:\\15.jpg");//write to D-drive
Byte[] Bte=new byte[1024];
int length=0;
while ((Length=input.read (BTE)) >0) {
Out.write (bte,0,length);
}
Input.close ();
Out.close ();
}

Batch: When testing, use a batch process rather than using loops. Executeupdate () Save about half the time
@Test
public void Usebatch () throws sqlexception{
String sql= "INSERT into [Test]. [dbo]. [User] ([Name],[age]) VALUES (?,?) ";
PreparedStatement ps=con.preparestatement (SQL);
Long Noe=system.currenttimemillis ();
for (int j = 0; J < 10000; J + +) {
Ps.setobject (1, "SP" +j);
Ps.setobject (2,J);
Ps.executeupdate (); The original time is 5875 milliseconds
Ps.addbatch ();

}
Ps.executebatch ();

System.out.println ("Add 10,000 times for:" + (System.currenttimemillis ()-noe));
}

Time stamp
@Test
public void TestDate () throws sqlexception{
String sql= "INSERT into [Test]. [dbo]. [User] ([Name],[age],[starttime],[endtime]) VALUES (?,?,?,?) ";
PreparedStatement ps=con.preparestatement (SQL);
Ps.setstring (1, "Test name");
Ps.setint (2,20);
Ps.settimestamp (3,new Timestamp (System.currenttimemillis ()));
Ps.setdate (4,new date (System.currenttimemillis ()));//This date is a. sql package under, not a. util bag, MO-lead the wrong bag!
int zong=ps.executeupdate ();
System.out.println ("timestamp" +zong);

}

Test rollback does not commit
@Test
public void Testtransaction () throws sqlexception{
Con.setautocommit (false);//settings are not automatically submitted by default
try {
String sql= "Update [User] set money=money-100 where id=5";
Statement state=con.createstatement ();
State.executeupdate (SQL);
int a=10/0;
sql= "Update [User] set money=money+100 where id=1";
State.executeupdate (SQL);
Con.commit ();//Commit a transaction
} catch (Exception e) {
Con.rollback ();
}
You can also use JDBC to roll things back! If not set the default does not automatically commit, then execute to int a=10/0; do not go down, the transfer will be deducted money, no money error!
}

}

Also, here is the use of rs.getmetadata () when performing a read operation. getColumnCount (); and Rs.getmetadata (). getColumnName () is useful;

Private List<map<string,object>> Setmap () throws sqlexception{
List<map<string,object>> list=new arraylist<map<string,object>> ();
Statement state=con.createstatement ();

String sql= "SELECT * from [User]";
ResultSet rs=state.executequery (SQL);
while (Rs.next ()) {
Map<string,object> map=new hashmap<string, object> ();
for (int i = 1; I <=rs.getmetadata (). getColumnCount (); i++) {
Map.put (Rs.getmetadata (). getColumnName (i), rs.getobject (i));
}
List.add (map);
}
Rs.close ();
State.close ();
Con.close ();
return list;
}
@Test
public void Getmap () throws sqlexception{
List<user> users=new arraylist<user> ();
List<map<string,object>> List=setmap ();
For (map<string, object> map:list) {
User User=new user ();
User.setage (Integer.valueof ((Map.get ("Age"). ToString ()));
User.setid (Integer.valueof ((Map.get ("id"). toString ()));
User.setname (Map.get ("name"). toString ());
Users.add (user);
}

for (User item:users) {
P (Item.getid () + "\ T" +item.getage () + "\ T" +item.getname ());
}
}




@Test
public void GetList () throws sqlexception{
List<list> list=setlist ();
for (List list2:list) {
for (Object object:list2) {
P (object);
}
}
}
Private list<list> setlist () throws sqlexception{
Statement state=con.createstatement ();
List<list> list=new arraylist<list> ();
String sql= "SELECT * from [User]";
ResultSet rs=state.executequery (SQL);
while (Rs.next ()) {
List l=new ArrayList ();
for (int i = 1; I <= rs.getmetadata (). getColumnCount (); i++) {
L.add (Rs.getobject (i));
}
List.add (l);
}
Rs.close ();
State.close ();
Con.close ();
return list;

}

Refresher Course JDBC: Connect to database using configuration file properties, database access picture, batch processing, timestamp, things rollback, etc.