eclipseでmysqlを使う【JDBCベース】

23653 ワード
java web
1.まずmysql-connector-java-5.1などの依存パッケージを探します.39-bin.JArはWebContent/WEB-INF/libフォルダの下に置く
2 .Project-Properties-Java Build Path-Libraries-Add JARs追加の選択
3 .プロジェクト内の新しいPackage名mysql
4.次の2つのクラスをmysqlパッケージに追加
package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.Timer;
import java.util.concurrent.locks.ReentrantReadWriteLock;
import java.util.concurrent.locks.ReentrantReadWriteLock.WriteLock;

public class ConnectionPool {
private String jdbcDriver = "com.mysql.jdbc.Driver"; //      
private String dbUrl = "jdbc:mysql://localhost:3306/"; //    URL
private String dbUsername = ""; //       
private String dbPassword = ""; //        
private String testTable = ""; //              ，       

private int initialConnections = 4; //         
private int incrementalConnections = 4;//           
private int maxConnections = 32; //         

private int needFreeTime = 1800000;//               ，    
private int freeCheckTime = 1800000;//                   

//               ,      null，         PooledConnection  
private List connections = null; 

private ReentrantReadWriteLock lock = new ReentrantReadWriteLock();
private WriteLock wl = lock.writeLock();

private Timer timer = new Timer();

public ConnectionPool(String dbUsername,String dbPassword,String dbName,String testTable) {
	this.dbUsername = dbUsername;
	this.dbPassword = dbPassword;
	dbUrl = dbUrl + dbName;
	this.testTable = testTable;
}

/**
* 
*           ，                  initialConnections      
 * @throws ClassNotFoundException 
 * @throws IllegalAccessException 
 * @throws InstantiationException 
 * @throws SQLException 
*/
public void createPool() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
	try {
		wl.lock();
		//          
		//           ，        connections     
		if (connections != null) {
			return; //       ，   
		}
		
		Class.forName(this.jdbcDriver);
		//Tools.log("   jdbc");
		
		//     JDBC Driver          
		//Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
		//DriverManager.registerDriver(driver); //    JDBC     
		
		//           ,      0    
		connections = new ArrayList();
		//    initialConnections      ，    。
		createConnections(this.initialConnections);
		
		timer.schedule(new MyTask(),freeCheckTime,freeCheckTime);
	} 
	finally{
		wl.unlock();
	}
}

class MyTask extends java.util.TimerTask{
	@Override
	public void run() {
	// TODO Auto-generated method stub
	    //             
	    if (connections == null) {  
	        System.out.println("       ，     !");  
	        return;  
	    }  
	    
	    List dels = new ArrayList();
	    long time0 = System.currentTimeMillis();
	    int s = 0;
	    
		try {
			wl.lock();
		    for(int i=0;i needFreeTime) {  
		            dels.add(pConn);
		        }  
		    }
		    
		    //         
		    if(connections.size() - dels.size() < initialConnections){
		    	s = initialConnections - connections.size() + dels.size();
		    }

		    //            
		    for(int i=s; i 0 && this.connections.size() >= this.maxConnections) {
		  break;
		}
		//            （   connections  ）
		int id = connections.size()+1;
		connections.add(new PooledConnection( newConnection() ,id));
	}
}

/**
*                
* 
* @return              
*/
private Connection newConnection() throws SQLException {
	//          
	Connection conn = DriverManager.getConnection(dbUrl, dbUsername,dbPassword);
	/*
	//               ，      ，           
	//         
	// connections.size()==0             
	if (connections.size() == 0) {
		DatabaseMetaData metaData = conn.getMetaData();
		int driverMaxConnections = metaData.getMaxConnections();
		//        driverMaxConnections    0 ，          
		//     ，              
		// driverMaxConnections         ，               
		//                             ,         
		//                
		if (driverMaxConnections > 0 && this.maxConnections > driverMaxConnections) {
			this.maxConnections = driverMaxConnections;
		}
	}*/
	return conn; //             
}

/**
*      getFreeConnection()                ,               ，             
*  （         ），            。
* 
* @return               
 * @throws SQLException 
*/
public Connection getConnection() throws SQLException{
	try {
		wl.lock();
		//          
		if (connections == null) {
		return null; //        ，    null
		}
		Connection conn = getFreeConnection(); //             
		//              ，           
		//    for               ，              
		//         ，                ，          ，             。
		int i=0;
		while(conn == null) {
			++i;
			//      
			//System.out.println("Wait");
			wait(200);
			//     ，         ，        10       ，  null
			conn = getFreeConnection(); 
			if(i==10){
				break;
			}
			// getFreeConnection()      null
			//                    
		}
		
		return conn;//           
	} 
	finally{
		wl.unlock();
	}
}

/**
*           connections               ，               ，      
* incrementalConnections               ，       。      ，           ，    null
* 
* @return             
 * @throws SQLException 
*/
private Connection getFreeConnection() throws SQLException {
	//                  
	Connection conn = findFreeConnection();
	if (conn == null) {
		//                
		//       
		createConnections(incrementalConnections);
		//               
		conn = findFreeConnection();
		if (conn == null) {
		//                  ，    null
		return null;
		}
	}
	return conn;
}

/**
*            ，            ，          ，   null
* 
* @return             
*/

private Connection findFreeConnection() {
	Connection conn = null;
	PooledConnection pConn = null;
	try {
		//        ，         
		for(int i=0;i< connections.size();++i){
			pConn = connections.get(i);
			if (!pConn.isBusy()) {
				//        ，                
				conn = pConn.getConnection();
				pConn.setBusy(true);

				//          
				if (!testConnection(conn)) {
					//           ，         ，
					//             ，      ，   null
					conn.close();//        
					conn = newConnection();//        
					pConn.setConnection(conn);
				}
				break; //            ，  
			}
		}
	} catch (SQLException e) {
		if(pConn != null){
			pConn.setBusy(false);//     ，       ，           ，
			                                 //setBusy              
		}
		e.printStackTrace();
		System.out.println("          ！ " + e.getMessage());
		return null;
	}
	return conn;//           
}

/**
*           ，     ，       false        true
* 
* @param conn
*                      
* @return    true        ， false      
*/

private boolean testConnection(Connection conn) {
	try {
		//          
		if (testTable ==null || testTable.equals("")) {
			//        ，         setAutoCommit()   
			//         （            ，      ,
			//     ）。  ：           
			//conn.setAutoCommit(true);
			return conn.isValid(500);
		} else {//               
			// check if this connection is valid
			Statement stmt = conn.createStatement();
			stmt.execute("select count(*) from " + testTable);
		}
	} catch (SQLException e) {
		//       ，       ，    false;
		return false;
	}
	//     ，   true
	return true;
}

/**
*                  ，         。                             。
* 
* @param              
*/

public void returnConnection(Connection conn) {
	//        ，        （   ），    
	if (connections == null) {
		System.out.println("       ，             !");
		return;
	}

	PooledConnection pConn = null;
	try {
		wl.lock();
		//            ，            
		for(int i=0;i< connections.size();++i){
			pConn = connections.get(i);
			//                 
			if (conn == pConn.getConnection()) {
			//     ,           
			pConn.setBusy(false);
			//System.out.println("  ："+pConn.ID());
			break;
			}
		}
	}
	finally{
		wl.unlock();
	}
}

/**
*            ，      。
*/

public void closeConnectionPool() {//////////////////////////////////////////////////////////////////////////////////////////////
	timer.cancel();
	
	//        ，     ，  
	if (connections == null) {
		System.out.println("       ，     !");
		return;
	}

	try {
		wl.lock();
		PooledConnection pConn = null;
		Iterator enumerate = connections.iterator();
		while (enumerate.hasNext()) {
			pConn = (PooledConnection) enumerate.next();
			//    ，  3  
			if (pConn.isBusy()) {
				wait(3000); //   3  
			}
			// 3        
			closeConnection(pConn.getConnection());
			System.out.println("    ");
			//           
			enumerate.remove();
		}
		//       
		connections = null;
	}
	finally{
		wl.unlock();
	}
}

/**
*          
* 
* @param           
*/

private void closeConnection(Connection conn) {
	try {
		conn.close();
	} catch (SQLException e) {
		System.out.println("          ： " + e.getMessage());
	}
}
	/**
	*            
	* 
	* @param       
	*/
	
private void wait(int mSeconds) {
	try {
	Thread.sleep(mSeconds);
	} catch (InterruptedException e) {
    }
}
/**
* 
*                             ，         ，                   。
*/

class PooledConnection {
	private Connection connection = null;//      
	private boolean busy = false; //             ，        
	public long lastTime = 0;//            
	int id=0;
	
	public int ID()
	{
		return id;
	}
	
	//     ，     Connection      PooledConnection   
	public PooledConnection(Connection connection,int id) {
		this.connection = connection;
		lastTime = System.currentTimeMillis();
		this.id = id;
	}
	
	//          
	public Connection getConnection() {
		return connection;
	}
	
	//       ，  
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	//          
	public boolean isBusy() {
		return busy;
	}
	
	//           
	public void setBusy(boolean busy) {
		this.busy = busy;
		lastTime = System.currentTimeMillis();
	}
}

}

  
package mysql;
import java.math.BigDecimal;
import java.sql.*;

/**
 *  JDBC   ，   mysql      
 * @author IBM
 *
 */
public class EasySQL {
	//  mysql    
	private static String userName = null;
	
	//  mysql   
	private static String password = null;
	
	//mysql        
	private static String dbname = null;
	
	//     
	private static ConnectionPool poolInstance = null;  
	
	//          
	private Connection conn = null;
	
	//JDBC     
	private PreparedStatement pstmt = null;
	
	//          
	private ResultSet rs = null;
	
	public static void init(String name,String key,String dbName) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
	{
		userName = name;
		password = key;
		dbname = dbName;
		
		if(poolInstance == null){
			poolInstance = new ConnectionPool(userName,password ,dbname ,null);
			poolInstance.createPool();
		}
	}
	
	public static void close()
	{
		if(poolInstance != null){
			poolInstance.closeConnectionPool();
			poolInstance = null;
		}
	}
	
	public static String status()
	{
		if(poolInstance != null){
			return poolInstance.status();
		}
		else{
			return "       ";
		}
	}
	
	/**
	 *          (  )
	 * @return
	 */
	public static long size()
	{
		 EasySQL.ReturnedData rd = EasySQL.query(
				 "select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema='"+dbname+"'");
		 long  size = rd.getLong(0,0);
		 return size;
	}
	
	//    ，             
	public static boolean updata(String sql)
	{
		 if(poolInstance != null){
				Connection conn = null;
				try {
					conn = poolInstance.getConnection();
					Statement  stmt = conn.createStatement();
					stmt.executeUpdate(sql);
					stmt.close() ; 
					return true;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally{
					poolInstance.returnConnection(conn);
				}
		}
		 return false;
	}
	
	//    ，        
	public static  ReturnedData query(String sql)
	{
		 if(poolInstance != null){
				Connection conn = null;
				try {
					conn = poolInstance.getConnection();
					Statement  stmt = conn.createStatement();
				    ResultSet rs =	stmt.executeQuery(sql);
					ReturnedData rd = new ReturnedData(rs);
					rs.close();
					stmt.close() ; 
				    return rd;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				finally{
					poolInstance.returnConnection(conn);
				}
		}
		 return null;
	}
	
	/**
	 * 
	 * @param statement PreparedStatement   
	 * @throws SQLException 
	 */
	public EasySQL(String statement)
	{
		if(poolInstance  != null){
			try {
				conn =  poolInstance.getConnection();
				pstmt = conn.prepareStatement(statement);  
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	
	/**
	 *          
	 * @param order     ， 1  
	 * @param value    
	 * @throws SQLException 
	 */
	public void setParam(int order,String value) 
	{
		try {
			pstmt.setString(order, value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 *          
	 * @param order     ， 1  
	 * @param value    
	 * @throws SQLException 
	 */
	public void setParam(int order,int value)
	{
		try {
			pstmt.setInt(order, value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 *          
	 * @param order     ， 1  
	 * @param value    
	 * @throws SQLException 
	 */
	public void setParam(int order,float value)
	{
		try {
			pstmt.setFloat(order, value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 *          
	 * @param order     ， 1  
	 * @param value    
	 * @throws SQLException 
	 */
	public void setParam(int order,double value)
	{
		try {
			pstmt.setDouble(order, value);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 *          
	 * @param order     ， 1  
	 * @param value    
	 * @throws SQLException 
	 */
	public void setParam(int order,long value)
	{
		try {
			pstmt.setBigDecimal(order, new BigDecimal(value));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void setParam(int order,byte[] data)
	{
		try {
			pstmt.setBytes(order, data);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 *          ，        addStatement setParam
	 * @param id     
	 * @throws SQLException 
	 */
	public boolean write()
	{
		if(pstmt != null){
			try {
				pstmt.executeUpdate();
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}
	
	/**
	 *          ，        addStatement setStatementParam
	 * @param id     
	 * @return 
	 * @throws SQLException 
	 */
	public ReturnedData read()
	{
		if(pstmt != null){
			try {
				rs = pstmt.executeQuery();
				ReturnedData rd = new ReturnedData(rs);
			    return rd;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	
	
	
	/**
	 *                  
	 * @throws SQLException 
	 */
	public void release()
	{
		try {
			if(rs != null){
				rs.close();
				rs = null;
			}
			
	        if(pstmt != null){
	        	  pstmt.close() ;   
	        	  pstmt = null;
		    }   
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
         if(poolInstance != null){
          	poolInstance.returnConnection(conn);
         }
	}
	
	public static class ReturnedData{
		
		public ReturnedData(ResultSet rs) throws SQLException{
			ResultSetMetaData rd = rs.getMetaData();
			cols = rd.getColumnCount();
	
			rs.last();
			resultCount = rs.getRow();
			data = new Object[resultCount][cols];
			
			_types = new String[cols];
			
			for(int i=0;i

説明:接続プールのdbUrl="jdbc:mysql://localhost:3306/「localhostとは、自機にインストールされているmysqlを指し、3306ポートとは、インストールされているmysqlの通信ポートを指します.
Windowsでmysqlを操作するには:
まずmysqlサービスが起動していることを確認します(一般的にmysqlをインストールするとシステムサービスリストに追加されます).mysqlを操作するにはcmdコマンドプロンプトを使用します.
ログイン:mysql-u root-p(パスワードを入力する必要があります)
データベースの作成:create***
操作するデータベースuse***を選択
いくつかのコマンド:
mysql -u root -p
CREATE DATABASE kyd; CREATE USER 'client'@'%' IDENTIFIED BY '1011'; GRANT ALL ON kyd.* TO 'client'@'%';
use test; show tables; DROP TABLE xxx; truncate table Data2; truncateはmysqlテーブルを保持する構造に相当し、このテーブルを再作成し、すべてのステータスが新しいテーブルに相当します.
ALTER TABLE data2 ADD INDEX user (user);
CREATE TABLE Data_n( ctime BIGINT not NULL, atime BIGINT default 0, folder INTEGER default 0, filesID INTEGER default 0, user INTEGER default 0, flag INTEGER default 0, creator INTEGER default 0, title VARBINARY(255), files VARBINARY(2046), texts VARCHAR(255), users VARCHAR(1470), PRIMARY KEY (ctime)) DEFAULT CHARSET=ASCII;
CREATE TABLE Text1( id INTEGER not NULL, txt VARCHAR(512), PRIMARY KEY ( id )) DEFAULT CHARSET=utf8;
CREATE TABLE Text2( id INTEGER not NULL, txt VARCHAR(4096), PRIMARY KEY ( id )) DEFAULT CHARSET=utf8;               CREATE TABLE Text3( id INTEGER not NULL, txt VARCHAR(16384), PRIMARY KEY ( id )) DEFAULT CHARSET=utf8;                   CREATE TABLE Text4( id INTEGER not NULL, txt MEDIUMTEXT,  PRIMARY KEY ( id )) DEFAULT CHARSET=utf8;
CREATE TABLE Text_df( ctime BIGINT not NULL, title VARCHAR(128), body TEXT, PRIMARY KEY ( ctime )) DEFAULT CHARSET=utf8;
CREATE TABLE Text_df ( ctime BIGINT NOT NULL PRIMARY KEY, title VARCHAR(128), body TEXT, FULLTEXT (title,body) WITH PARSER ngram ) ENGINE=InnoDB CHARACTER SET utf8;
//指定したデータベースのテーブルサイズの表示
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='test' and table_name='data';
select * from data order by ctime desc limit 0,1;
linuxでmysqlを操作する
コマンド#service mysqld statusまたは#service mysql statusコマンドを使用してmysqlの起動状態を表示mysqld is stoppedの場合はmysqlサービスが停止状態であり、mysqld is runningの場合はmysqlサービスが起動状態であることを示します.
詳細は、このブログlinuxカテゴリの下の記事を参照してください.
linuxでmysqlを操作してputtyを使用してサーバーに接続するとwindowsのコマンドプロンプトモードとあまり差がありません.
EasySQLクラスの使用
(1)初期化とクローズ
ContextListenerリスニングクラス
EasySQL.init(Config.attr("mysql", "name"), Config.attr("mysql", "key"), Config.attr("mysql", "db"));
EasySQL.close();
(2)SQL文の実行
読み込み:
		EasySQL es = new EasySQL("select * from "+sheet+" where ctime="+ctime);
		
		EasySQL.ReturnedData rd =	es.read();
		
		for(int i=0;i 0)
				{title = new String(b1,"utf-8");}
				
				if(b2 != null && b2.length > 0)
				{files = new String(b2,"utf-8");}
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    String texts = rd.getString(i, 11);
		    String users = rd.getString(i, 12);
		 }
		es.release();

書き込み操作
EasySQL es = new EasySQL("insert into "+sheet+" (ctime,atime,folder,l2) values (?,?,?,?)");
		    es.setParam(1,ctime);
		    es.setParam(2,ctime);
			es.setParam(3,folder);
			es.setParam(4,order);
			es.write();
			es.release(); 


	  String sql = "UPDATE "+sheet+" SET files = ? ,atime = ?  WHERE ctime = "+ctime;
	  EasySQL es = new EasySQL(sql);
	  try {
		 byte[] b = value.getBytes("utf-8");
		  es.setParam(1,b);
		  es.setParam(2,System.currentTimeMillis());
		  es.write();
		  es.release();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	  String sql = "UPDATE "+sheet+" SET texts = ?  WHERE ctime = "+ctime;
	  EasySQL es = new EasySQL(sql);
	  es.setParam(1,value);
	  es.write();
	  es.release();



	  String sql = "UPDATE "+sheet+" SET title = ?  WHERE ctime = "+ctime;
	  EasySQL es = new EasySQL(sql);
		 
		try {
			byte[] b = value.getBytes("utf-8");
			es.setParam(1,b);
		    es.write();
		    es.release();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}



	  String sql = "UPDATE "+sheet+" SET files = ?  ,texts = ? ,atime = ?  WHERE ctime = "+ctime;
	  EasySQL es = new EasySQL(sql);
	  try {
		byte[] b = value.getBytes("utf-8");
		 es.setParam(1,b);
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 
	  es.setParam(2,value2);
	  es.setParam(3,System.currentTimeMillis());
	  es.write();
	  es.release();



EasySQL.updata("delete from "+sheet+" where ctime="+ctime);




	  String sql = "UPDATE "+sheet+" SET folder=? , files = ? ,texts = ? , filesID=?,creator=?, user=?, flag=?, atime = ?,title=?,users=?  WHERE ctime = "+sm.ctime;
	  EasySQL es = new EasySQL(sql);
	  es.setParam(1,sm.folder);
	  
	  es.setParam(3,sm.texts);
	  es.setParam(4,sm.filesID);
	  es.setParam(5,sm.creator);
	  es.setParam(6,sm.user);
	  es.setParam(7,sm.flag);
	  es.setParam(8,System.currentTimeMillis());
	  
	  es.setParam(10,sm.users);
	  try {
		byte[] b1 = sm.files.getBytes("utf-8");
		byte[] b2 = sm.title.getBytes("utf-8");
		es.setParam(2,b1);
		es.setParam(9,b2);
	} catch (UnsupportedEncodingException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  es.write();
	  es.release();
  }

GitHub flavored markdown をローカルでプレビューする
aftereffects スクリプトでレイヤースタイル（境界線）を適用する方法