[sqlite example]
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
/**
*
* @author sqlitetutorial.net
*/
public class SqliteExample {
/**
* Connect to a sample database
*/
public static void connect() {
Connection conn = null;
try {
// db parameters
String url = "jdbc:sqlite:test.db";
// create a connection to the database
conn = DriverManager.getConnection(url);
System.out.println("Enter an id");
String input1 = System.console().readLine();
System.out.println("Enter an name");
String input2 = System.console().readLine();
Employee toAdd = new Employee(Integer.parseInt(input1), input2);
createEmployee(conn, toAdd);
List<Employee> employees = getEmployees(conn, input1);
for(Employee e: employees){
System.out.println(e.id+" "+e.name);
}
System.out.println("Connection to SQLite has been established.");
// run some more code use the same connection...
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
connect();
}
public static void createEmployee(Connection conn, Employee emp)
throws SQLException {
PreparedStatement stmt = conn.prepareStatement(
"insert into employees values (?,?);"
);
stmt.setInt(1, emp.id);
stmt.setString(2, emp.name);
stmt.executeUpdate();
stmt.close();
}
public static void saveEmployee(Connection conn, Employee emp)
throws SQLException {
PreparedStatement stmt = conn.prepareStatement(
"update employees set name = ? where id = ?;"
);
stmt.setInt(2, emp.id);
stmt.setString(1, emp.name);
stmt.executeUpdate();
stmt.close();
}
public static List<Employee> getEmployees(Connection conn, String input)
throws SQLException {
Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery("select id, name from employees where id="+input);
List<Employee> results = new ArrayList<>();
while(rs.next()){
int id = rs.getInt("id"); // rs.getInt(1);
String name = rs.getString("name"); // rs.getString();
Employee emp = new Employee(id, name);
results.add(emp);
}
rs.close();
stmt.close();
return results;
}
public static class Employee{
int id;
String name;
Employee(int id, String name){
this.id = id;
this.name = name;
}
}
}