자바로 SQLite에서 SQL 사용하기

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

동영상 강의

contacts 테이블에 Insert/Update/Delete 하기 1

package kr.ac.jj.japanteam;

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

public class J002_SqliteInsertUpdateDelte1st {

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

		Connection conn = null;
		PreparedStatement ps = null;

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

		String sql = "INSERT INTO contact(name, phone, addr, post) VALUES('홍길동', '010-1111-1111', '전라북도 전주시', '51111');";
//		String sql = "UPDATE contact SET phone='010-5555-5555' WHERE name='길동이';";
//		String sql = "DELETE FROM contact WHERE name='홍길동';";
		try {
			ps = conn.prepareStatement(sql);
			int r = ps.executeUpdate();
			if(r > 0) {
				System.out.println("저장/수정/삭제 성공");
			}
			else {
				System.out.println("저장/수정/삭제 실패");
			}
		} catch (Exception 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.japanteam;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class J002_SqliteInsertUpdateDelte1st {

public static void main(String[] args) {

// TODO Auto-generated method stub

String DBFileName = "d:\\addr.db";

Connection conn = null;

PreparedStatement ps = null;

try {

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

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

} catch (Exception e) {

e.printStackTrace();

}

String sql = "INSERT INTO contact(name, phone, addr, post) VALUES('홍길동', '010-1111-1111', '전라북도 전주시', '51111');";

// String sql = "UPDATE contact SET phone='010-5555-5555' WHERE name='길동이';";

// String sql = "DELETE FROM contact WHERE name='홍길동';";

try {

ps = conn.prepareStatement(sql);

int r = ps.executeUpdate();

if(r > 0) {

System.out.println("저장/수정/삭제 성공");

}

else {

System.out.println("저장/수정/삭제 실패");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

contacts 테이블에 Select 하기 1

package kr.ac.jj.japanteam;

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

public class J002_SqliteSelect1st {

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

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

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

		String sql = "SELECT * FROM contact;";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while(rs.next()) {
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				String addr = rs.getString("addr");
				String post = rs.getString("post");
				//int speed = rs.getInt("speed");
				System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post);
			}
		} catch (Exception 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.japanteam;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class J002_SqliteSelect1st {

public static void main(String[] args) {

// TODO Auto-generated method stub

String DBFileName = "d:\\addr.db";

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

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

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

} catch (Exception e) {

e.printStackTrace();

}

String sql = "SELECT * FROM contact;";

try {

ps = conn.prepareStatement(sql);

rs = ps.executeQuery();

while(rs.next()) {

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

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

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

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

//int speed = rs.getInt("speed");

System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

contacts 테이블에 Insert/Update/Delete 하기 2

package kr.ac.jj.japanteam;

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

public class J002_SqliteInsertUpdateDelte2nd {

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

		Connection conn = null;
		PreparedStatement ps = null;

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

		String sql = "INSERT INTO contact(name, phone, addr, post) VALUES(?,?,?,?);";
//		String sql = "UPDATE contact SET phone=? WHERE name=?;";
//		String sql = "DELETE FROM contact WHERE name=?;";
		try {
			ps = conn.prepareStatement(sql);
			//Insert 경우
			ps.setString(1, "홍길동");
			ps.setString(2, "010-1111-1111");
			ps.setString(3, "전라북도 전주시");
			ps.setString(4, "51111");
			//ps.setInt(5,  3); //정수값을 넣을때는 setInt() 사용

			//Update 경우
			//ps.setString(1, "010-5555-5555");
			//ps.setString(2, "홍길동");

			//Delete 경우
			//ps.setString(1, "홍길동");

			int r = ps.executeUpdate();
			if(r > 0) {
				System.out.println("저장/수정/삭제 성공");
			}
			else {
				System.out.println("저장/수정/삭제 실패");
			}
		} catch (Exception 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.japanteam;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class J002_SqliteInsertUpdateDelte2nd {

public static void main(String[] args) {

// TODO Auto-generated method stub

String DBFileName = "d:\\addr.db";

Connection conn = null;

PreparedStatement ps = null;

try {

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

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

} catch (Exception e) {

e.printStackTrace();

}

String sql = "INSERT INTO contact(name, phone, addr, post) VALUES(?,?,?,?);";

// String sql = "UPDATE contact SET phone=? WHERE name=?;";

// String sql = "DELETE FROM contact WHERE name=?;";

try {

ps = conn.prepareStatement(sql);

//Insert 경우

ps.setString(1, "홍길동");

ps.setString(2, "010-1111-1111");

ps.setString(3, "전라북도 전주시");

ps.setString(4, "51111");

//ps.setInt(5, 3); //정수값을 넣을때는 setInt() 사용

//Update 경우

//ps.setString(1, "010-5555-5555");

//ps.setString(2, "홍길동");

//Delete 경우

//ps.setString(1, "홍길동");

int r = ps.executeUpdate();

if(r > 0) {

System.out.println("저장/수정/삭제 성공");

}

else {

System.out.println("저장/수정/삭제 실패");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

contacts 테이블에 Select 하기 2

package kr.ac.jj.japanteam;

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

public class J002_SqliteSelect2nd {

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

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

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

		String sql = "SELECT * FROM contact WHERE post like ?;";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, "52222");
			//ps.setInt(2, 10);//?에 정수값이 들어갈때 사용
			rs = ps.executeQuery();

			while(rs.next()) {
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				String addr = rs.getString("addr");
				String post = rs.getString("post");
				//int speed = rs.getInt("speed");  //테이블의 필드가 integer 형 일때 사용
				System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post);
			}
		} catch (Exception 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.japanteam;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class J002_SqliteSelect2nd {

public static void main(String[] args) {

// TODO Auto-generated method stub

String DBFileName = "d:\\addr.db";

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

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

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

} catch (Exception e) {

e.printStackTrace();

}

String sql = "SELECT * FROM contact WHERE post like ?;";

try {

ps = conn.prepareStatement(sql);

ps.setString(1, "52222");

//ps.setInt(2, 10);//?에 정수값이 들어갈때 사용

rs = ps.executeQuery();

while(rs.next()) {

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

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

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

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

//int speed = rs.getInt("speed"); //테이블의 필드가 integer 형 일때 사용

System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

DataAccessObject 클래스로 재구성

package kr.ac.jj.japanteam;

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

public class J002_SqliteTotal {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		DataAccessObject dao = new DataAccessObject();
		dao.select();
		dao.insert("홍길동", "010-1111-1111","전라북도 전주시", "51111");
		dao.select();
		dao.update("홍길동", "010-5555-5555");
		dao.select();
		dao.delete("길동이");
		dao.select_post("51111");

	}
}



class DataAccessObject {
	String DBFileName = "d:\\addr.db";

	public Connection getConn() {
		Connection conn = null;

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

		return conn;
	}

	public boolean insert(String name, String phone, String addr, String post) {
		String sql = "INSERT INTO contact(name, phone, addr, post) VALUES(?,?,?,?);";
		Connection conn = null;
		PreparedStatement ps = null;
		int r = 0;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, phone);
			ps.setString(3, addr);
			ps.setString(4, post);
			//ps.setInt(5,  3); //?에 정수값을 넣을때는 setInt() 사용

			r = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

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

		if(r > 0) return true;
		else return false;
	}

	public boolean update(String name, String phone) {
		String sql = "UPDATE contact SET phone=? WHERE name=?;";
		Connection conn = null;
		PreparedStatement ps = null;
		int r = 0;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, phone);
			ps.setString(2, name);
			//ps.setInt(5,  3); //?에 정수값을 넣을때는 setInt() 사용

			r = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

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

		if(r > 0) return true;
		else return false;
	}

	public boolean delete(String name) {
		String sql = "DELETE FROM contact WHERE name=?;";
		Connection conn = null;
		PreparedStatement ps = null;
		int r = 0;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			//ps.setInt(5,  3); //?에 정수값을 넣을때는 setInt() 사용

			r = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

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

		if(r > 0) return true;
		else return false;
	}

	public void select() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "SELECT * FROM contact;";
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			//ps.setInt(2, 10);//?에 정수값이 들어갈때 사용
			rs = ps.executeQuery();

			while(rs.next()) {
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				String addr = rs.getString("addr");
				String post2 = rs.getString("post");
				//int speed = rs.getInt("speed");  //테이블의 필드가 integer 형 일때 사용
				System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post2);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

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

	public void select_post(String post) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "SELECT * FROM contact WHERE post like ?;";
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, post);
			//ps.setInt(2, 10);//?에 정수값이 들어갈때 사용
			rs = ps.executeQuery();

			while(rs.next()) {
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				String addr = rs.getString("addr");
				String post2 = rs.getString("post");
				//int speed = rs.getInt("speed");  //테이블의 필드가 integer 형 일때 사용
				System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post2);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

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

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

package kr.ac.jj.japanteam;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class J002_SqliteTotal {

public static void main(String[] args) {

// TODO Auto-generated method stub

DataAccessObject dao = new DataAccessObject();

dao.select();

dao.insert("홍길동", "010-1111-1111","전라북도 전주시", "51111");

dao.select();

dao.update("홍길동", "010-5555-5555");

dao.select();

dao.delete("길동이");

dao.select_post("51111");

}

class DataAccessObject {

String DBFileName = "d:\\addr.db";

public Connection getConn() {

Connection conn = null;

try {

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

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

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

public boolean insert(String name, String phone, String addr, String post) {

String sql = "INSERT INTO contact(name, phone, addr, post) VALUES(?,?,?,?);";

Connection conn = null;

PreparedStatement ps = null;

int r = 0;

try {

conn = getConn();

ps = conn.prepareStatement(sql);

ps.setString(1, name);

ps.setString(2, phone);

ps.setString(3, addr);

ps.setString(4, post);

//ps.setInt(5, 3); //?에 정수값을 넣을때는 setInt() 사용

r = ps.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

if(r > 0) return true;

else return false;

}

public boolean update(String name, String phone) {

String sql = "UPDATE contact SET phone=? WHERE name=?;";

Connection conn = null;

PreparedStatement ps = null;

int r = 0;

try {

conn = getConn();

ps = conn.prepareStatement(sql);

ps.setString(1, phone);

ps.setString(2, name);

//ps.setInt(5, 3); //?에 정수값을 넣을때는 setInt() 사용

r = ps.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

if(r > 0) return true;

else return false;

}

public boolean delete(String name) {

String sql = "DELETE FROM contact WHERE name=?;";

Connection conn = null;

PreparedStatement ps = null;

int r = 0;

try {

conn = getConn();

ps = conn.prepareStatement(sql);

ps.setString(1, name);

//ps.setInt(5, 3); //?에 정수값을 넣을때는 setInt() 사용

r = ps.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

if(r > 0) return true;

else return false;

}

public void select() {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

String sql = "SELECT * FROM contact;";

try {

conn = getConn();

ps = conn.prepareStatement(sql);

//ps.setInt(2, 10);//?에 정수값이 들어갈때 사용

rs = ps.executeQuery();

while(rs.next()) {

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

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

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

String post2 = rs.getString("post");

//int speed = rs.getInt("speed"); //테이블의 필드가 integer 형 일때 사용

System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post2);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

public void select_post(String post) {

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

String sql = "SELECT * FROM contact WHERE post like ?;";

try {

conn = getConn();

ps = conn.prepareStatement(sql);

ps.setString(1, post);

//ps.setInt(2, 10);//?에 정수값이 들어갈때 사용

rs = ps.executeQuery();

while(rs.next()) {

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

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

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

String post2 = rs.getString("post");

//int speed = rs.getInt("speed"); //테이블의 필드가 integer 형 일때 사용

System.out.println(name + "\t" + phone + "\t" + addr + "\t" + post2);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

과제 1 : 위 6번을 완성해서 제출하시오.
과제 2 : student 테이블을 이용해서 위 6번과 같은 코드를 완성하시오.

whan0623

517