11번째 프로그램(성적처리) — DB 사용하기

Posted on: 2019년 04월 03일 2019년 04월 03일
Categories: 강의자료, 자바

DB Browser for Sqlite를 이용하여 D:\sungjuk.db 파일생성
1. highstudent 테이블 생성

S011_Sungjuk_Insert.java : Sqlite DB에 성적 저장

package kr.ac.jj.java201812345;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class S011_Sungjuk_Insert {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Scanner scan = new Scanner(System.in);

		String name = scan.nextLine();
		int kor = scan.nextInt();
		int eng = scan.nextInt();
		int mat = scan.nextInt();

		String DBFileName = "D:\\hongildong.db";

		String sql = "INSERT INTO highstudent(name, kor, eng, mat) ";
		sql += " VALUES(?, ?, ?, ?)";

		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + DBFileName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, kor);
			ps.setInt(3, eng);
			ps.setInt(4, mat);
			int r = ps.executeUpdate();

			if(r > 0) System.out.println("성공");
			else System.out.println("실패");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

package kr.ac.jj.java201812345;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Scanner;

public class S011_Sungjuk_Insert {

public static void main(String[] args) {

// TODO Auto-generated method stub

Scanner scan = new Scanner(System.in);

String name = scan.nextLine();

int kor = scan.nextInt();

int eng = scan.nextInt();

int mat = scan.nextInt();

String DBFileName = "D:\\hongildong.db";

String sql = "INSERT INTO highstudent(name, kor, eng, mat) ";

sql += " VALUES(?, ?, ?, ?)";

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + DBFileName);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

PreparedStatement ps = null;

try {

ps = conn.prepareStatement(sql);

ps.setString(1, name);

ps.setInt(2, kor);

ps.setInt(3, eng);

ps.setInt(4, mat);

int r = ps.executeUpdate();

if(r > 0) System.out.println("성공");

else System.out.println("실패");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

S011_Sungjuk_Select.java : Sqlite DB에 저장된 성적을 읽어와 출력

package kr.ac.jj.java201812345;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class S011_Sungjuk_Select {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String DBFileName = "D:\\hongildong.db";

		String sql = "SELECT * FROM highstudent ";

		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + DBFileName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			HighStudent.PrintHead();
			while(rs.next()) {
				String name = rs.getString("name");
				int kor = rs.getInt("kor");
				int eng = rs.getInt("eng");
				int mat = rs.getInt("mat");
				HighStudent hs = new HighStudent(name, kor, eng, mat);
				hs.PrintNew();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

package kr.ac.jj.java201812345;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class S011_Sungjuk_Select {

public static void main(String[] args) {

// TODO Auto-generated method stub

String DBFileName = "D:\\hongildong.db";

String sql = "SELECT * FROM highstudent ";

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + DBFileName);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

PreparedStatement ps = null;

ResultSet rs = null;

try {

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

HighStudent.PrintHead();

while(rs.next()) {

String name = rs.getString("name");

int kor = rs.getInt("kor");

int eng = rs.getInt("eng");

int mat = rs.getInt("mat");

HighStudent hs = new HighStudent(name, kor, eng, mat);

hs.PrintNew();

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

whan0623

517