mysql与2008 r2_JDBC例程（MySQL和SQLServer2008R2）

importjava.io.UnsupportedEncodingException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.Properties;/*** @(#)DataBase.java 010/04/22

*@authorDudu

* EST.All rights reserved*/

public classMySQLTest {/**定义一个Connection 用来连接数据库*/

private Connection conn=null;/**连接数据库的URL*/

//private final String url="jdbc:mysql://localhost:3306/memo";

private final String url="jdbc:jtds:sqlserver://172.30.112.104:1433;datebaseName=memo";//private final String url="jdbc:sqlserver://127.30.112.104:1433;DatabaseName=memo";

/**指定数据的用户名和密码*/

private final String username="memo";private final String password="Iccc2014";/**定义一个int记录更新的记录数量*/

int count=0;/**定义一个结果集 用于放回查询结果*/

private ResultSet resultSet=null;private PreparedStatement pstmt=null;publicMySQLTest(){

conn=connectionDB();

}/*** 建立数据的连接

*@exceptionSQLException, ClassNotFoundException*/@SuppressWarnings("finally")publicConnection connectionDB(){try{//Class.forName("com.mysql.jdbc.Driver");

Class.forName("net.sourceforge.jtds.jdbc.Driver");//Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//Properties prop = new Properties();//prop.put(username, "memo");//prop.put(password, "Iccc2014");

conn=DriverManager.getConnection(url,username,password);//conn=DriverManager.getConnection(url,prop);

Statement stmt =conn.createStatement();

String sql0= "USE memo";

stmt.execute(sql0);

System.out.println("连接数据库成功");

}catch(Exception e){

e.printStackTrace();

System.out.println("建立数据库发生错误！");

}finally{returnconn;

}

}/*** 查询方法

*@paramsql查询sql语句

*@returnresultSet*/@SuppressWarnings("finally")publicResultSet query(String sql){try{

pstmt=conn.prepareStatement(sql);/**查询*/resultSet=pstmt.executeQuery();

}catch(SQLException e) {

e.printStackTrace();

}finally{returnresultSet;

}

}/*** 更新数据

*@paramsql 更新sql语句

*@return

*/

public intupdate(String sql){try{

pstmt=conn.prepareStatement(sql);

count=pstmt.executeUpdate();

}catch(SQLException e) {

e.printStackTrace();

System.out.println("执行更新出错了");

}returncount;

}/**关闭连接*/

public booleancoles(){boolean isColes = false;if(resultSet!=null){try{

resultSet.close();

resultSet=null;

isColes=true;

}catch(SQLException e) {

isColes=false;

e.printStackTrace();

System.out.println("关闭结果集发生错误");

}

}if(pstmt!=null){try{

pstmt.close();

pstmt=null;

isColes=true;

}catch(SQLException e) {

isColes=false;

e.printStackTrace();

System.out.println("关闭pstmt发生异常");

}

}if(conn!=null){try{

conn.close();

conn=null;

isColes=true;

}catch(Exception e) {

isColes=false;

e.printStackTrace();

System.out.println("关闭conn发生异常");

}

}returnisColes;

}/*** 测试查询的方法

*@throwsSQLException*/

public void testQuery() throwsSQLException{

resultSet=query("select * from tb_memo");if(resultSet.next()){

System.out.println(resultSet.getString(1));

System.out.println(resultSet.getString(2));

System.out.println(resultSet.getString(3));

System.out.println(resultSet.getString(4));

}

}public void testUpdate() throwsUnsupportedEncodingException{

String memo= "测试";

count= update("insert into TB_MEMO(CARDNO,CERTIFICATENO,ISURGENT,MEMO,OPID) values('123456711212','33022619900621001xx','N','"+memo+"', '130480')");if(count>0){

System.out.println("更新成功");

}

}/***

*@paramargs

*@throwsSQLException

*@throwsClassNotFoundException*/

public static void main(String[] args) throwsSQLException, ClassNotFoundException, UnsupportedEncodingException {

MySQLTest db= newMySQLTest();/**调用查询方法*/

//db.testQuery();

/**调用更新方法*/db.testUpdate();/**调用关闭连接方法*/db.coles();

}

}