[티베로]jsp, java JDBC로 연결하기

기본소스만 제공하겠습니다.

 

public class BbsDAO {
	
	
	
	private String ip = "222.10.00.100"; //디비아이피
	private String port = "8629"; //연결포트 
	private String database = "name";//데이터베이스 
	private String users = "admin"; //유저이름
	private String password = "pasword"; //비밀번호
	private final String DRIVER_NAME = "com.tmax.tibero.jdbc.ext.TbConnectionPoolDataSource";//티베로 databaseresoure생성시 참고
	private final String TIBERO_JDBC_URL= "jdbc:tibero:thin:@" + ip + ":" + port + ":" + database;




// connection:db에접근하게 해주는 객체
	Statement pstmt =null;
	private Connection conn; // connection:db에접근하게 해주는 객체
	private PreparedStatement pstmt;
	private ResultSet rs;



	// mysql 처리부분

	public BbsDAO() {
		// 생성자를 만들어준다.
		connect();
		} 
		
		public void connect() {
			try {
				Class.forName(DRIVER_NAME);
				} 
			catch (ClassNotFoundException e) {
				System.err.println("Class Not Found"); 
				} try {
					conn = DriverManager.getConnection(TIBERO_JDBC_URL, users, password); }
				catch (SQLException e) { System.err.println("DB Connection Error");
				} 
		}

	

	//현재의 시간을 가져오는 함수

	public String getDate() { 
		String SQL = "SELECT to_char(sysdate,'YYYYMMDDHH24MISS') from dual";
		try {
			Statement pstmt = conn.createStatement();
			rs = pstmt.executeQuery(SQL);
			if(rs.next()) {
				return rs.getString(1);

			}

		} catch (Exception e) {

			e.printStackTrace();

		}

		return ""; //데이터베이스 오류

	}

	

	//bbsID 게시글 번호 가져오는 함수

		public int getNext() { 

			String SQL = "SELECT bbsID FROM BBS ORDER BY bbsID DESC";

			try {

				Statement pstmt = conn.createStatement();

				rs = pstmt.executeQuery(SQL);

				if(rs.next()) {

					return rs.getInt(1) + 1;

				}

				return 1;//첫 번째 게시물인 경우

			} catch (Exception e) {

				e.printStackTrace();

			}

			return -1; //데이터베이스 오류

		}

		

		//실제로 글을 작성하는 함수

		public int write(String bbsTitle, String userID, String bbsContent) { 

			String SQL = "INSERT INTO BBS(bbsid,bbstitle,userid,bbscontent,bbsAvailable) VALUES(?, ?, ?, ?, ?)";

			try {

				PreparedStatement pstmt = conn.prepareStatement(SQL);

				pstmt.setInt(1, getNext());

				pstmt.setString(2, bbsTitle);

				pstmt.setString(3, userID);

				pstmt.setString(4, bbsContent);

				pstmt.setInt(5,1);
				

				return pstmt.executeUpdate();

				

			} catch (Exception e) {

				e.printStackTrace();

			}

			return -1; //데이터베이스 오류

		}

		void disconnect() {
			if (conn != null) {
				
				try { conn.close();
				} 
				catch (SQLException e) { 
					System.err.println("Disconnect Error");
					}

			} 


		}
		

		public ArrayList<Bbs> getList(int pageNumber){ 

			String SQL = "SELECT bbsid,bbstitle,userid,nvl(bbsdate,sysdate),bbscontent,bbsAvailable FROM BBS WHERE bbsID < ? and bbsAvailable = 1 and rownum < 10 ORDER BY bbsID DESC";

			ArrayList<Bbs> list = new ArrayList<Bbs>();

			try {

				PreparedStatement pstmt = conn.prepareStatement(SQL);

				pstmt.setInt(1, getNext() - (pageNumber -1) * 10);

				rs = pstmt.executeQuery();

				while (rs.next()) {

					Bbs bbs = new Bbs();

					bbs.setBbsID(rs.getInt(1));

					bbs.setBbsTitle(rs.getString(2));

					bbs.setUserID(rs.getString(3));

					bbs.setBbsDate(rs.getString(4));

					bbs.setBbsContent(rs.getString(5));

					bbs.setBbsAvailable(rs.getInt(6));

					list.add(bbs);

				}

			} catch (Exception e) {

				e.printStackTrace();

			}

			return list; 

		}
		public Bbs getBbs(int bbsID) {

			String SQL = "SELECT * FROM BBS WHERE bbsID = ?";

			try {

				PreparedStatement pstmt = conn.prepareStatement(SQL);

				pstmt.setInt(1, bbsID);

				rs = pstmt.executeQuery();

				if (rs.next()) {

					Bbs bbs = new Bbs();

					bbs.setBbsID(rs.getInt(1));

					bbs.setBbsTitle(rs.getString(2));

					bbs.setUserID(rs.getString(3));

					bbs.setBbsDate(rs.getString(4));

					bbs.setBbsContent(rs.getString(5));

					bbs.setBbsAvailable(rs.getInt(6));



					return bbs;

				}

			} catch (Exception e) {

				e.printStackTrace();

			}

			return null;



		}
		public boolean nextPage (int pageNumber) {

			String SQL = "SELECT bbsid,bbstitle,userid,nvl(bbsdate,sysdate),bbscontent,bbsAvailable FROM BBS WHERE bbsID < ? and bbsAvailable = 1 and rownum>10  ORDER BY bbsID DESC ";

			ArrayList<Bbs> list = new ArrayList<Bbs>();

			try {

				PreparedStatement pstmt = conn.prepareStatement(SQL);

				pstmt.setInt(1, getNext() - (pageNumber -1) * 10);

				rs = pstmt.executeQuery();

				if (rs.next()) {

					return true;

				}

			} catch (Exception e) {

				e.printStackTrace();

			}

			return false; 		

		}


		//수정 함수

		public int update(int bbsID, String bbsTitle, String bbsContent) {

				String SQL = "UPDATE BBS SET bbsTitle = ?, bbsContent = ? WHERE bbsID = ?";

				try {

					PreparedStatement pstmt = conn.prepareStatement(SQL);

					pstmt.setString(1, bbsTitle);

					pstmt.setString(2, bbsContent);

					pstmt.setInt(3, bbsID);

					return pstmt.executeUpdate();



				} catch (Exception e) {

					e.printStackTrace();

				}

				return -1; // 데이터베이스 오류

			}
		public int delete(int bbsID) {

			String SQL = "UPDATE BBS SET bbsAvailable = 0 WHERE bbsID = ?";

			try {

				PreparedStatement pstmt = conn.prepareStatement(SQL);   

				pstmt.setInt(1, bbsID);

				return pstmt.executeUpdate();

			} catch (Exception e) {

				e.printStackTrace();

			}
			return -1; // 데이터베이스 오류
		}
}

we잡소스에  insert ,update select문 예제가 있으니 참고