The story of making dto, dao-like with java, sqlite

Hello, this is a certain company engineer. A memorandum because I made dto, dao-like when making a task management application with swing, java. Click here for related past articles ↓ https://qiita.com/Ohtak/items/1be20a4b06b5833c3d61 https://qiita.com/Ohtak/items/f9a47b9be26c93fedaf9

1. Code

First from the dto file. I haven't done anything different, though ...

TaskDto.java

public class TaskDto {

	//Task ID
	Integer id;
	
	//Task name
	String title;
	
	//Task remarks
	String discription;
	
	//Task deadline
	String limitDate;
	
	//Task status
	Integer status;
	
	/**
	 * @return id
	 */
	public Integer getId() {
		return id;
	}

	/**
	 * @param id set id
	 */
	public void setId(Integer id) {
		this.id = id;
	}
	
	/**
	 * @return title
	 */
	public String getTitle() {
		return title;
	}

	/**
	 * @param title set title
	 */
	public void setTitle(String title) {
		this.title = title;
	}
	
	/**
	 * @return discription
	 */
	public String getDiscription() {
		return discription;
	}

	/**
	 * @param discription set discription
	 */
	public void setDiscription(String discription) {
		this.discription = discription;
	}
	
	/**
	 * @return title
	 */
	public String getLimitDate() {
		return limitDate;
	}

	/**
	 * @param title set title
	 */
	public void setLimitDate(String limitDate) {
		this.limitDate = limitDate;
	}
	
	/**
	 * @return status
	 */
	public Integer getStatus() {
		return status;
	}

	/**
	 * @param status set status
	 */
	public void setStatus(Integer status) {
		this.status = status;
	}
}

That's it.

Next is dao. I don't look like dao so much, so I give it a selfish class name.

DBAccesser.java

public class DBAccesser {
	public Connection con = null;
	public Statement smt = null;
	
	public List<TaskDto> selectAll(){
		try {
    		//Created if not connected to the database
    		con = DriverManager.getConnection("jdbc:sqlite:/Users/taskManager.sqlite");
    		smt = con.createStatement();
    		String sql = "select * from task";
    		ResultSet rs = smt.executeQuery(sql);
    		List<TaskDto> tasks = new ArrayList<TaskDto>();
    		while( rs.next() ) {
    			TaskDto task = new TaskDto();
    			task.setId(rs.getInt(1));
        		task.setTitle(rs.getString(2));
        		task.setDiscription(rs.getString(3));
        		task.setLimitDate(rs.getString(4));
        		task.setStatus(rs.getInt(5));
        		tasks.add(task);
    		}
    		con.close();
    		return tasks;
    	}
		catch (SQLException e) {
    		//TODO auto-generated catch block
    		e.printStackTrace();
    		return null;
    	}
	}
	
	public int selectLastId(){
		try {
    		//Created if not connected to the database
    		con = DriverManager.getConnection("jdbc:sqlite:/Users/taskManager.sqlite");
    		smt = con.createStatement();
    		String sql = "select id from task order by id desc limit 1";
    		ResultSet rs = smt.executeQuery(sql);
    		int id = rs.getInt(1);
    		con.close();
    		return id;
    	}
		catch (SQLException e) {
    		//TODO auto-generated catch block
    		e.printStackTrace();
    		return 0;
    	}
	}
	
	public void insert(TaskDto task){
		try {
    		//Connect to database
    		con = DriverManager.getConnection("jdbc:sqlite:/Users/taskManager.sqlite");
    		smt = con.createStatement();
    		String sql = "insert into task (id, name, limit_date, comment) values(" + String.valueOf(task.getId()) + ", '" + task.getTitle() + "', '" + task.getLimitDate() + "', '" + task.getDiscription() + "');";
    		smt.executeUpdate(sql);
    		con.close();
    	} catch (SQLException e) {
    		//TODO auto-generated catch block
    		e.printStackTrace();
    	}
	}
	
	public void update(TaskDto task){
		try {
			//Connect to database
			con = DriverManager.getConnection("jdbc:sqlite:/Users/taskManager.sqlite");
			smt = con.createStatement();
			String sql = "update task set name='"+ task.getTitle() + "', limit_date='" + task.getLimitDate() + "', comment='"+ task.getDiscription() + "', status = " + task.getStatus() + " where id=" + task.getId() + ";";
			smt.executeUpdate(sql);
			con.close();
		} catch (SQLException e) {
			//TODO auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void delete(int id){
		try {
			//Connect to database
			con = DriverManager.getConnection("jdbc:sqlite:/Users/taskManager.sqlite");
			smt = con.createStatement();
			String sql = "DELETE FROM task WHERE id=" + id + ";";
			smt.executeUpdate(sql);
			con.close();
		} catch (SQLException e) {
			//TODO auto-generated catch block
			e.printStackTrace();
		}
	}
}

In addition to the four basic methods, there is only one original method. I haven't done that difficult here either. I just did it according to the sqlite manual.

2. Caller

It looks like this when calling.

taskRegisterPanel.java

DBAccesser dbAccesser = new DBAccesser();
TaskDto task = new TaskDto();
task.setId(Integer.parseInt(taskId.getText()));
task.setTitle(taskTitle.getText());
task.setLimitDate(taskLimit.getText());
task.setDiscription(taskDiscription.getText());
task.setStatus(0);
dbAccesser.update(task);

This is also easy.

3. Impressions

The advantage of using this is that it makes it easier for the caller to call the operations together. It's easy to do above because I'm using it. It was very annoying because I didn't use it at first ...

By using the dto file, you can easily operate the arguments and results when accessing the DB.

The more I write it, the more I feel it now, but I will continue to do my best.