Добрый день.
скажите пожалуйста есть у кого-то клас/пример лучше живой код организации работы между базой даных и веб сервером(Tomcat,JSP,Struts).
Мой клас не подходит, вылетает на ошибке Too many connections to server(web server!!!)....
Data source rejected establishment of connection, message from server: "Too many connections"
| Код |
package edu.university.database;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
public class DataBase {
private static DataBase db = null;
private static String CANNOT_FIND_DRIVER = "Cannot find driver.";
private static String CANNOT_CREATE_STATEMENT = "Cannont create the statement.";
private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
private static final String CONNECTION_STRING = "jdbc:mysql://ifdevserver:3307/kafedra";
private DataSource ds;
public void DataBase() {
}
public void init() throws Exception{
System.out.println("Loading DB driver...");
try {
Class.forName(DRIVER_NAME);
}
catch (ClassNotFoundException ex) {
System.out.println(CANNOT_FIND_DRIVER);
ex.printStackTrace();
}
System.out.println("Loading DB driver...DONE");
System.out.println("Setuping DataSource...");
ds = setupDataSource(CONNECTION_STRING);
System.out.println("Setuping DataSource...DONE");
db = this;
}
public DataSource setupDataSource(String connectURI) {
ObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,"root","");
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
return dataSource;
}
public static DataBase getInstance(){
if (db == null) {
System.gc();
DataBase db_reserv = new DataBase();
try {
db_reserv.init();
} catch (Exception ex) {
System.out.println("Can't initialize database connection.:" + ex.getMessage());
}
return db_reserv;
}
return db;
}
public Connection getConnection() {
Connection conn = null;
try {
conn = ds.getConnection();
} catch (SQLException ex) {
System.out.println("Can't get connection(1):" + ex.getMessage());
ds = null;
db = null;
System.gc();
try {
init();
Thread.sleep(100);
conn = ds.getConnection();
} catch (Exception exx) {
System.out.println("Can't get connection(2):" + ex.getMessage());
}
}
return conn;
}
}
|
Пример использования
| Код |
package edu.university.database.utils;
import edu.university.database.DataBase;
import edu.university.objects.TypeShadow;
import edu.university.objects.LineInSchedule;
import edu.university.utils.constants.Constants;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collection;
import java.util.ArrayList;
import java.util.List;
public class ScheduleManager {
Connection connection = null;
private static ScheduleManager scheduleManager = null;
public static ScheduleManager getInstance() {
if(scheduleManager == null) {
scheduleManager = new ScheduleManager();
}
return scheduleManager;
}
public ScheduleManager() {
connection = DataBase.getInstance().getConnection();
}
public void close() {
try {
connection.close();
connection = null;
}
catch (SQLException sex) {
System.out.println("Closing connection,statements error.:" + sex.getMessage());
}
scheduleManager = null;
System.gc();
}
public List getSchedule(int groupId,int dayId){
List result = new ArrayList();
ResultSet resSet = null;
try {
Statement statement = connection.createStatement();
// POSSIBLE UA, EN
resSet = statement.executeQuery("select * from schedule where groupId=" + groupId +" and dayid="+dayId+ " order by n");
int i = 0;
while (resSet.next()) {
LineInSchedule line = new LineInSchedule();
line.setDayId(resSet.getInt("dayid"));
line.setGroupId(resSet.getInt("groupId"));
line.setSubjectIdTop(resSet.getInt("subjectTopId"));
line.setRoomIdTop(resSet.getInt("roomTopId"));
line.setTypeIdTop(resSet.getInt("kindTopId"));
line.setSubjectIdDown(resSet.getInt("subjectDownId"));
line.setRoomIdDown(resSet.getInt("roomDownId"));
line.setTypeIdDown(resSet.getInt("kindDownId"));
line.setN(resSet.getInt("n"));
result.add(line);
i++;
if (i>Constants.LESSONS_NUMBER) {
System.out.println("======>>>> SOME ERROR <<<<=============");
break;
}
}
resSet.close();
} catch (SQLException sex) {
System.out.println("Error while getting schedule of all days.:" +sex.getMessage());
}
return result;
}
public boolean updateSchedule(List schedule) {
boolean result = true;
// schedule(groupId,dayid,subjectTopId,roomTopId,kindTopId,subjectDownId,roomDownId,kindDownId)
Statement statement = null;
try {
statement = connection.createStatement();
for(int i=0;i<schedule.size();i++) {
LineInSchedule line = (LineInSchedule)schedule.get(i);
String SQL = "UPDATE schedule SET " +
"subjectTopId="+line.getSubjectIdTop() + "," +
"roomTopId=" + line.getRoomIdTop() + "," +
"kindTopId=" + line.getTypeIdTop() + "," +
"subjectDownId="+line.getSubjectIdDown() + "," +
"roomDownId=" + line.getRoomIdDown() + "," +
"kindDownId=" + line.getTypeIdDown() + " WHERE n=" + line.getN();
//ystem.out.println(SQL);
result = result&(statement.executeUpdate(SQL)>0);
}
statement.close();
statement = null;
} catch (SQLException e) {
result = false;
System.out.println("Error updating schedule.");
e.printStackTrace();
}
finally {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
System.out.println("Error while closing statement");
e.printStackTrace();
}
}
}
return result;
}
}
|
|
)