xsql.xcs

浏览该文件的文档.

//xlang 
 
package Sql{
    public class SqlException : Exception{
        public SqlException(){
            
        }
        public SqlException(int err, String sqlmessage){
            errcode = err;
            message = sqlmessage;
        }
    };
    
    public class DatabaseNotSupportException : SqlException{
        public DatabaseNotSupportException(String sqlmessage){
            errcode = 0;
            message = "not support method:" + sqlmessage;
        }
    };
    
    public interface ConnPoolCallback{
        Connection createConnection();
        bool alive(Connection conn);
    };
    
    public class ConnectionPool{
        static class connectorMgr{
            public Connection pconnection;
            public long timestamp;
        };
        
        List<connectorMgr> _pool = new List<connectorMgr>();
        Map<int, bool> _error_map = new Map<int, bool>();
        
        static const int TIMEOU_LIMIT = 300000;
        
        int _maxConnect;
        int _active_cnt, _wait_time;
        int _validtesttime, _maxIdleTime, _maxIdle;
        
        ConnPoolCallback callback;
        
 
        Connection allocate(){
            Connection connector = callback.createConnection();
            if (connector != nilptr){
                _active_cnt++;
            }
            return connector;
        }
 
        public Connection getConnector(){
            Connection connector = nilptr;
            long lastactive = 0;
 
            synchronized(_pool){
                if (_maxConnect != 0){
                    while ((_pool.size() == 0) && (_active_cnt >= _maxConnect)){
                        _pool.wait();
                    }
                }
 
                do{
                    if (connector != nilptr){
                        _active_cnt--;
                        connector.close();
                        connector = nilptr;
                    }
                    
                    lastactive = 0;
 
                    List.Iterator<connectorMgr> iter = _pool.iterator();
                    if (iter.hasNext()){
                        connectorMgr mgr = iter.get();
                        connector = mgr.pconnection;
                        lastactive = mgr.timestamp;
                        _pool.remove(iter);
                    }else{
                        break;
                    }
 
                } while (test(connector, lastactive) == false);
 
                if (connector == nilptr){
                    if ((_maxConnect == 0) || (_active_cnt < _maxConnect)){
                        connector = allocate();
                    }
                }
            }
            return connector;
        }
 
        bool test(Connection conn, long lastactive){
            if ( (_system_.currentTimeMillis() - lastactive) > _validtesttime){
                return callback.alive(conn);
            }
            return true;
        }
 
        public void recycle(Connection connection){
        
            int mysql_error = connection.getErrorCode();
 
            if (_error_map.containsKey(mysql_error) == false){
                synchronized(_pool){
                    connectorMgr mgr = new connectorMgr();
                    mgr.pconnection = connection;
                    mgr.timestamp = _system_.currentTimeMillis();
 
                    if (_maxIdleTime != 0){
                        List.Iterator<connectorMgr> it = _pool.iterator();
 
                        while (it != nilptr && it.hasNext()){
                            List.Iterator<connectorMgr> tmp = it.getNext();
                            connectorMgr pmt = it.get();
                            
                            if ((mgr.timestamp - pmt.timestamp) > _maxIdleTime){
                                pmt.pconnection.close();
                                _pool.remove(it);
                                _active_cnt--;
                            }
                            it = tmp;
                        }
                    }
 
                    
 
                    if (_maxIdle != 0){
                        while (_pool.size() > _maxIdle){
                            List.Iterator<connectorMgr> tmp = _pool.iterator();
                            connectorMgr pmt = tmp.get();
                            pmt.pconnection.close();
                            _pool.remove(tmp);
                            _active_cnt--;
                        }
                    }
                    _pool.add(mgr);
                    _pool.notify();
                }
            } else{
                connection.close();
                synchronized(_pool){
                    _active_cnt--;
                }
            }
        }
 
        public bool config(ConnPoolCallback _callback, String reset_error, int maxWait, int maxConnect, int maxIdle, int maxIdleTime, int validtesttime){
        
            if (maxIdle != 0 && maxConnect != 0 && maxIdle >= maxConnect){
                throw new IllegalArgumentException("Connector config failed : maxIdle >= maxConnect\n");
                return false;
            }
 
            if (_callback == nilptr){
                return false;
            }
            
            callback = _callback;
            
            _validtesttime = validtesttime;
 
            if (reset_error != nilptr){
                String err_list = reset_error;
                String[] elist = err_list.split(',');
                for (int i = 0; i < elist.length; i++){
                    String str = elist[i];
                    str.trim(true);
                    _error_map.put(str.parseInt(), true);
                }
            }
 
            if (maxWait != -1){
                _wait_time = maxWait;
            }
 
            _maxIdle = maxIdle;
            _maxConnect = maxConnect;
            _maxIdleTime = maxIdleTime;
            return true;
        }
 
        public void clear(){
            synchronized(_pool){
                _pool.clear();
                _error_map.clear();
                _wait_time = 0;
                _maxConnect = 0;
                _maxIdle = 0;
                _active_cnt = 0;
                _maxIdleTime = TIMEOU_LIMIT;
            }
        }
    };
    
    public class Database{
        static Map<String, ConnectionRegister> _database = new Map<String, ConnectionRegister>();
        
        public static bool reigstry(String name, ConnectionRegister reg){
            synchronized(_database){
                if (_database.containsKey(name) == false){
                    _database.put(name, reg);
                    return true;
                }
            }
            return false;
        }
        
        public static Connection getConnection(String driverName){
            synchronized(_database){
                try{
                    ConnectionRegister cr = _database.get(driverName);
                    return cr.allocConnection(driverName);
                }catch(Exception e){
                
                }
            }
            return nilptr;
        }
        
        
    };
    
    public interface ConnectionRegister{
        Connection allocConnection(String drivername);
    };
    
    public interface Connection{
        void create(String uri, String username, String pwd);
        
        void setOption(int opt, Object option);
        
        Object getOption(int opt);
        
        PreparedStatement prepareStatement(String sql);
        
        Statement createStatement();
        
        int getErrorCode();
        
        String getError();
        
        bool isClosed();
        
        void close();
    };
    
    
    public class ResultSet{
        public bool first();
        public bool last();
 
        public bool next();
        public bool previous();
 
        public bool isFirst();
        public bool isLast();
        public bool isEof();
        public bool isValid();
        
        public int getInt(String columnlabel);
        public long getLong(String columnlabel);
        public float getFloat(String columnlabel);
        public double getDouble(String columnlabel);
        public byte getByte(String columnlabel);
        public bool getBoolean(String columnlabel);
        public String getString(String columnlabel);
 
        public int getInt(int columnIndex);
        public long getLong(int columnIndex);
        public float getFloat(int columnIndex);
        public double getDouble(int columnIndex);
        public byte getByte(int columnIndex);
        public bool getBoolean(int columnIndex);
        public String getString(int columnIndex);
        public String getColumnName(int i);
        public int findColumn(String);
        public int getColumnCount();
        public long getRowCount();
        public int getRow();
        public void close();
        public int getColumnType(int ){return 0;}
    };
    
    public class Statement{
        public int execute(String sql);
        public ResultSet executeQuery(String sql);
        public int executeUpdate(String sql);
        public ResultSet getResult();
        public void close();
        public int get_changes(){return 0; }
        public int get_lastInsertId(){return 0 ;}
    };
    
    public static class PreparedStatement : Statement{
    
        String textSql;
        String []strsplit;
        String []strparams;
        int paramid = 0;
        public PreparedStatement(){}
        public PreparedStatement(String sql){
            textSql = " " + sql + " ";
            strsplit = textSql.split('?');
            if (strsplit.length == 0){
                throw new IllegalArgumentException("invalid sql");
            }
            strparams = new String[strsplit.length - 1];
        }
        
        public String safety(String str){
            str = str.replace("\\", "\\\\");
            str = str.replace("\"", "\\\"");
            str = str.replace("\'", "\\\'");
            str = str.replace("\`", "\\\`");
            return "'" + str + "'";
        }
        
        public int execute(){
            return execute(getSql());
        }
        
        public String getSql(){
            String sqlout = strsplit[0];
            int i = 1, c = strsplit.length;
            for (; i < c; i++){
                sqlout = sqlout + (strparams[i - 1] + strsplit[i]);
            }
            return sqlout;
        }
        
        public ResultSet executeQuery(){
            return executeQuery(getSql());
        }
        
        public int executeUpdate(){
            return executeUpdate(getSql());
        }
        
        public void setValue(String value){
            if (paramid < strparams.length){
                strparams[paramid++] = safety(value);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }
        }
        
        public void setValue(int value){
            if (paramid < strparams.length){
                strparams[paramid++] = String.format("%d",value);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }
        }
        
        public void setValue(long value){
            if (paramid < strparams.length){
                strparams[paramid++] = String.format("%d",value);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }
        }
        
        public void setValue(byte value){
            if (paramid < strparams.length){
                strparams[paramid++] = String.format("%02u",value & 0xff);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }
        }
        
        public void setValue(float value){
            if (paramid < strparams.length){
                strparams[paramid++] = "" + value;
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }
        }
        
        public void setValue(double value){
            if (paramid < strparams.length){
                strparams[paramid++] = "" + value;
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }
        }
        
        //--
        public void setString(String value){
            setValue(value);
        }
        
        public void setInt(int value){
            setValue(value);
        }
        
        public void setLong(long value){
            setValue(value);
        }
        
        public void setByte(byte value){
            setValue(value);
        }
        
        public void setDouble(double value){
            setValue(value);
        }
        
        public void setFloat(float value){
            setValue((double)value);
        }
        
        
        public void setValue(int id, String value){
            if ((id > 0) && (id - 1 < strparams.length)){
                strparams[id - 1] = safety(value);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }      
        }
        
        public void setValue(int id, int value){
            if ((id > 0) && (id - 1 < strparams.length)){
                strparams[id - 1] = String.format("%d",value);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }  
        }
        
        public void setValue(int id, long value){
            if ((id > 0) && (id - 1 < strparams.length)){
                strparams[id - 1] = String.format("%d",value);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }  
        }
        
        public void setValue(int id, byte value){
            if ((id > 0) && (id - 1 < strparams.length)){
                strparams[id - 1] = String.format("%02u",value & 0xff);
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }  
        }
        
        public void setValue(int id, float value){
            if ((id > 0) && (id - 1 < strparams.length)){
                strparams[id - 1] = "" + value;
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }  
        }
        
        public void setValue(int id, double value){
            if ((id > 0) && (id - 1 < strparams.length)){
                strparams[id - 1] = "" + value;
            }else{
                throw new SqlException(-1, "sql " +textSql + " dont need value:" + value);
            }  
        }
        
        //---
        public void setString(int id, String value){
            setValue(id, value);    
        }
        
        public void setInt(int id, int value){
            setValue(id, value);
        }
        
        public void setLong(int id, long value){
            setValue(id, value);
        }
        
        public void setByte(int id, byte value){
            setValue(id, value);
        }
        
        public void setFloat(int id, float value){
            setValue(id, value); 
        }
        
        public void setDouble(int id, double value){
            setValue(id, value);
        }
        
        public void setValues(Object [] args){
            for (int i = 0; i < args.length; i++){
                if (args[i].instanceOf(String)){
                    setValue((String)args[i]);
                }else
                if (args[i].instanceOf(int)){
                    setValue((int)args[i]);
                }else
                if (args[i].instanceOf(long)){
                    setValue((long)args[i]);
                }else
                if (args[i].instanceOf(byte)){
                    setValue((byte)args[i]);
                }else
                if (args[i].instanceOf(double)){
                    setValue((double)args[i]);
                }else{
                    throw new SqlException(-1, "sql " +textSql + " dont accept type:" + i);
                }
            }
        }
        
        public int get_changes()override{
            throw new SqlException(-1, "get_changes");
            return 0;
        }
        
        public void finalize(){
            close();
        }
    };
};
 
 
/*
 
void testSql1(){
    Sql.ConnectionPool pool = new Sql.ConnectionPool();
    pool.config(new Sql.ConnPoolCallback(){
    
        Sql.Connection createConnection()override{
            //TODO 
            Sql.Connection conn = Sql.Database.getConnection("sqlserver");
            if (conn != nilptr){
                conn.create("192.168.10.128", "sa", "233276111");
            }
            return conn;
        }
        
        bool alive(Sql.Connection conn)override{
            //TODO 
            Sql.Statement state =  conn.createStatement();
            try{
                state.execute("select 1;");
                return true;
            }catch(Exception e){
                
            }
            return false;
        }
    },"2006,2014",50, 50, 3, 300000, 10000);
    
    for (int i = 0; i < 30; i++){ 
        new Thread(){
            void run()override{
                testSqlite(pool);
            }
        }.start();
    }
}
 
int testSqlite(Sql.ConnectionPool pool){
 
    Sqlite.registry();
    
    _system_.createConsole();
    
    long before = _system_.currentTimeMillis();
 
    for (int cx = 0; cx < 100;cx ++){ 
        Sql.Connection slite = pool.getConnector();
    
        if (slite != nilptr){
 
            //slite.create("sqlite.db", nilptr, nilptr);
            //slite.create("127.0.0.1", "root", "233276111");
            
            //slite.setOption(Mysql.MysqlOption.CHARSET,"utf8");
            
            //slite.setOption(Mysql.MysqlOption.SELECTDB, "test");
            
            
            // 创建表
            Sql.PreparedStatement stmt = slite.prepareStatement("CREATE table member(name varchar(32),age int) CHARSET=utf8");
            
            //_system_.consoleWrite("time:" + (_system_.currentTimeMillis() - before) + " - 1\n");
            
            try{
                stmt.execute();
            }catch(Exception e){
                //_system_.consoleWrite("SqlException:" + e.getErrorCode() + "," + e.getMessage() + "\n");
            }
            
            String [] names = {
                "嘉懿","煜城","懿轩","烨伟","苑博","伟泽","熠彤","鸿煊","博涛","烨霖","烨华","煜祺","智宸","正豪",
                "昊然","明杰","立诚","立轩","立辉","峻熙","弘文","熠彤","鸿煊","烨霖","哲瀚","鑫鹏","致远","俊驰"
            };
            
            _system_.consoleWrite("time:" + (_system_.currentTimeMillis() - before) + " - before insert\n");
            
            try{
                //插入人名  年龄随机
                
                for(int i = 0,  c = names.length; i < c; i ++){
                    stmt = slite.prepareStatement("insert into member (name,age)VALUES(?, ?)");
                    stmt.setValue(names[i]);
                    stmt.setValue((int)(Math.random() * 80));
                    stmt.execute();
                }
            }catch(Exception e){
                _system_.consoleWrite(e.getMessage() + "\n");
            }
            _system_.consoleWrite("time:" + (_system_.currentTimeMillis() - before) + " - after insert\n");
 
        
            stmt = slite.prepareStatement("select * from member where age < ? limit 10");
            stmt.setValue(40);
            
            try{
                Sql.ResultSet rs = stmt.executeQuery();
                int c = rs.getRowCount();
                while (rs.next()){
                    //_system_.consoleWrite(new String(rs.getString("name").getBytes("GB18030//IGNORE")) + ":" + rs.getString("age") + "\n");
                }
            }catch(Exception e){
                _system_.consoleWrite(e.getMessage() + "\n");
            }
        }
        pool.recycle(slite);
    }
    
    _system_.consoleWrite("done time:" + (_system_.currentTimeMillis() - before) + " - 4\n");
    
    Thread.sleep(100);
    
    return 0;
}
 
 
 
void testSql(){
    //SqlServer.registry();
 
    Sql.ConnectionPool pool = new Sql.ConnectionPool();
    pool.config(new Sql.ConnPoolCallback(){
    
        Sql.Connection createConnection()override{
            //TODO 
            Sql.Connection conn = Sql.Database.getConnection("sqlserver");
            if (conn != nilptr){
                conn.create("192.168.10.128", "sa", "233276111");
            }
            return conn;
        }
        
        bool alive(Sql.Connection conn)override{
            //TODO 
            Sql.Statement state =  conn.createStatement();
            try{
                state.execute("select 1;");
                return true;
            }catch(Exception e){
                
            }
            return false;
        }
    },"2006,2014",50, 50, 3, 300000, 10000);
    
    for (int i = 0; i < 30; i++){ 
        new Thread(){
            void run()override{
                testSqlserver(pool);
            }
        }.start();
    }
}
 
int testSqlserver(Sql.ConnectionPool pool){
    _system_.createConsole();
 
   for (int aa = 0;aa < 1000; aa++){
        long before = _system_.currentTimeMillis();
        
        Sql.Connection slite = pool.getConnector();
        if (slite != nilptr){
            Sql.PreparedStatement stmt = slite.prepareStatement("CREATE table blog..member(name varchar(32),age int)");
            
            _system_.consoleWrite("time:" + (_system_.currentTimeMillis() - before) + " - 1\n");
            
            try{
                stmt.execute();
            }catch(Exception e){
                _system_.consoleWrite("SqlException:" + e.getErrorCode() + "," + new String(e.getMessage().getBytes("GB18030//IGNORE")) + "\n");
            }
            
            String [] names = {
                "嘉懿","煜城","懿轩","烨伟","苑博","伟泽","熠彤","鸿煊","博涛","烨霖","烨华","煜祺","智宸","正豪",
                "昊然","明杰","立诚","立轩","立辉","峻熙","弘文","熠彤","鸿煊","烨霖","哲瀚","鑫鹏","致远","俊驰"
            };
            
            _system_.consoleWrite("time:" + (_system_.currentTimeMillis() - before) + " - before insert\n");
            
            try{
                //插入人名  年龄随机
                
                for(int i = 0,  c = names.length; i < c; i ++){
                    stmt = slite.prepareStatement("insert into blog..member (name,age)VALUES(?, ?)");
                    stmt.setValue(names[i]);
                    stmt.setValue((int)(Math.random() * 80));
                    stmt.execute();
                }
            }catch(Exception e){
                _system_.consoleWrite(new String(e.getMessage().getBytes("GB18030//IGNORE")) + "\n");
            }
            _system_.consoleWrite("time:" + (_system_.currentTimeMillis() - before) + " - after insert\n");
 
        
            stmt = slite.prepareStatement("select top 10 * from blog..member where age < ?");
            stmt.setValue(40);
            
            try{
                Sql.ResultSet rs = stmt.executeQuery();
                int c = rs.getRowCount();
                while (rs.isEof() == false){
                    _system_.consoleWrite(new String(rs.getString("name").getBytes("GB18030//IGNORE")) + ":" + rs.getString("age") + "\n");
                    rs.next();
                }
            }catch(Exception e){
                _system_.consoleWrite(new String(e.getMessage().getBytes("GB18030//IGNORE")) + "\n");
            }
            pool.recycle(slite);
        }
        _system_.consoleWrite("done time:" + (_system_.currentTimeMillis() - before) + " - 4\n");
   }
    
    Thread.sleep(100);
    return 0;
}
*/
 
/*int main(String [] args){
    
    Mysql m = new Mysql();
    m.create("127.0.0.1","root","zt54TmMoRNLgf77M");
    m.setOption(Mysql.MysqlOption.CHARSET,"utf8");
    m.setOption(Mysql.MysqlOption.SELECTDB, "dcb");
    m.setOption(Mysql.MysqlOption.RET_CHANGES,0);
    Sql.PreparedStatement state = m.prepareStatement("select * from `db_branch` where id = ? limit 1");
    state.setValue(4);
    Sql.ResultSet res = state.executeQuery();
    while (res.next()){
        for (int i = 0; i < res.getColumnCount(); i++){
            String str = res.getString(i);
            _system_.output(str);
        }
    }
    
    state = m.prepareStatement("insert into `db_branch` (`cid`, `address`)values(?, ?)");
    state.setValue(1475);
    state.setValue("asdsasda");
    int rt = state.executeUpdate();
    int nid = state.get_lastInsertId();
    
    Sql.Statement stm = m.createStatement();
    stm.executeUpdate("insert into `db_branch` (`cid`, `address`)values(15365, 'weqw')");
    nid = stm.get_lastInsertId();
    
    return 0;
}
*/